(CakePHP2.x, SQLite3) paginateで生のSQLを使うには

【最終更新: 2012-11-30】

目次
  1. 前置き
  2. find()ではWHERE句をエスケープできない
  3. paginate()で生のSQLを使う
  4. ページング用の生のSQLから全件数を取得する
  5. 独自paginateの処理を検証
  6. 【お手軽!】内部結合の"table"キーに記述する


前置き

SQLワイルドカードとして扱われる%(パーセント)や_(アンダーバー)そのものを検索したい場合、自前でエスケープしなければなりません。
CakePHPfind()のパラメータの形式に則った場合と、生のSQLの場合、それぞれのエスケープの仕方は以前の記事で書きました。
http://d.hatena.ne.jp/sutara_lumpur/20120716/1342433052


find()ではWHERE句をエスケープできない

MySQLでは\(バックスラッシュ)を自動でエスケープ文字だと認識してくれますが、SQLite3ではESCAPE節で指定しなければなりません。

MySQL : WHERE name LIKE '%hoge\_%'
SQLite3 : WHERE name LIKE '%hoge\_%' ESCAPE '\'


これが曲者です。
find()の引数となる連想配列の内、ORDER BY句となるorderには生SQLを指定することもできますが、WHERE句となるconditionsでは生SQLを使えません。
クックブックにも書いてあります。


array(
'conditions' => array('Model.field' => $thisValue), //array of conditions
'recursive' => 1, //int
'fields' => array('Model.field1', 'DISTINCT Model.field2'), //array of field names
'order' => array('Model.created', 'Model.field3 DESC'), //string or array defining order
'group' => array('Model.field'), //fields to GROUP BY
'limit' => n, //int
'page' => n, //int
'offset' => n, //int
'callbacks' => true //other possible values are false, 'before', 'after'
)
【参考】
Retrieving Your Data — CakePHP Cookbook v2.x documentation
http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#find


まあ、しかし…、それならば"find()"ではなく、生SQLを実行する"query()"を使えばいいのです。
が! ページング機能を手軽に実現してくれる"paginate()"は、find()と同じ形式で検索条件を指定しなければなりません。


paginate()で生のSQLを使う

ここからが本題です。
モデル内に独自のpaginate()メソッドを追加することで、find()の形式に縛られない、自由な検索をすることができるようです。


【参考】
"paginateで複雑なSQL" フォーラム - CakePHP Users in Japan
http://cakephp.jp/modules/newbb/viewtopic.php?topic_id=1356&forum=6


Pagination — CakePHP Cookbook v2.x documentation
http://book.cakephp.org/2.0/en/core-libraries/components/pagination.html#custom-query-pagination


しかし、コントローラからモデル内の独自paginate()へ、どうやって生SQLを渡せばいいのか。
その方法が分からず、さんざん試行錯誤した末、ようやく下記の方法にたどり着きました。


(コントローラ)
$this->paginate = $sql; //$sqlの中身は生SQL
$this->set('data', $this->paginate(モデル名));

(モデル)
function paginate() {
$extra = func_get_arg(6);
//$extra['type']に生SQLが格納されている。
return $this->query($extra['type']);
}

コントローラの"$this->paginate"に生SQLを放り込んだら、モデル側の第7引数($extra)の"type"キーから取り出せる。
…判明してみれば、すごく簡単な手順です。
が、そこにたどり着くまでにどれほどの時間を…(つД`)
もう、疲れ果てました orz
ゆっくり体を休めて、鬱にならないようにします ∠(`・ω・´)


ページング用の生のSQLから全件数を取得する

【追記 2012-08-29】
ページング表示するデータの全件数などをビューで表示するには、"paginateCount()"もモデルで独自に作成する必要があります。
全件数を戻り値として返すだけで、後はCakePHPがうまくやってくれるようです。

(モデル)
function paginateCount() {
$extra = func_get_arg(2);
return count($this->query(
preg_replace(
'/LIMIT \d+ OFFSET \d+$/u',
'',
$extra['type']
)
));
}
今回、生のSQLは第3引数の"type"キーに入っています。($extra['type'])
しかし、そのまま"query()"を実行してはいけません。
LIMIT、OFFSET句を記述している場合は、それを取り除く処理が必要です。


ビューでは特に変更することはありません。
下記のように"Paginator"ヘルパーを普通に使うことができます。


(ビュー)
echo $this->Paginator->counter('全 {:count} 件');


【参考】
Paginator — CakePHP Cookbook v2.x documentation
http://book.cakephp.org/2.0/en/core-libraries/helpers/paginator.html#creating-a-page-counter


独自paginateの処理を検証

【追記 2012-11-30】
コメントで、モデルのpaginateの引数で使用しないものを切り捨てていることについての指摘がありましたので検証しました。
結果は、切り捨てても問題ありませんでした。
また、私が"func_get_arg()"を知らなかったばかりに、余計な引数を記述をしていました。
今回、記事内の該当箇所を訂正しています。


新旧の比較
特定ページの一覧を取得

//新
function paginate() {
$extra = func_get_arg(6);
return $this->query($extra['type']);
}
//旧
function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
return $this->query($extra['type']);
}
全件数を取得

//新
function paginateCount() {
$extra = func_get_arg(2);
return count($this->query(
preg_replace(
'/LIMIT \d+ OFFSET \d+$/u',
'',
$extra['type']
)
));
}
//旧
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
return count($this->query(
preg_replace(
'/LIMIT \d+ OFFSET \d+$/u',
'',
$extra['type']
)
));
}

新しい記述法なら余計な引数が目につくこともありませんが、一応、問題がないことを検証します。

検証1: モデル内paginate()の引数に渡された値を表示
結果は下記のとおりです。
目的の生SQLが入った第7引数の"type"キー以外は、重要な情報は入っていませんでした。

//モデル内の独自paginate()
function paginate($conditions, $fields, $order, $limit, $page, $recursive, $extra) {

//引数をprint_r()で表示
Array
(
[conditions] => Array
(
)

[fields] =>
[order] =>
[limit] => 20
[page] => 1
[recursive] => 1
[extra] => Array
(
[maxLimit] => 100
[paramType] => named
[type] => SELECT ... (生SQL)
)
)


検証2: 問題がないか、CakePHPのソースを読んで確認
(CakePHP)/lib/Cake/Controller/Component/PaginatorComponent.php
111行目以降に"paginate()"が記述されています。
抜粋して説明します。

//今回は$objectにはモデル名が入っています。
public function paginate($object = null, $scope = array(), $whitelist = array()) {

...

//各引数が"null"で初期化されています。
$conditions = $fields = $order = $limit = $page = $recursive = null;

...
この間、各引数に値が挿入されます。
が、今回は$objectが連想配列ではなく文字列のため、検証1のようにほとんど何も入りません。
...

//$objectのモデル内に"paginate"メソッドがあるかどうかで分岐しています。
if ($object->hasMethod('paginate')) {
//今回はこちらに分岐します。
$results = $object->paginate(
$conditions, $fields, $order, $limit, $page, $recursive, $extra
);
} else {
$parameters = compact('conditions', 'fields', 'order', 'limit', 'page');
if ($recursive != $object->recursive) {
$parameters['recursive'] = $recursive;
}
$results = $object->find($type, array_merge($parameters, $extra));
}

というわけで、第7引数以外は無視して構わないようです。

【お手軽!】内部結合の"table"キーに記述する

(追記 2013-07-15)
コメント欄で助言をいただきました。
下記のように、コントローラへの記述だけで生SQLをページング機能の中で使うことができます。
とてもお手軽ですね (*´∀`*)
xaitさん、ありがとうございました。
Postsコントローラのアクション内での記述

$this->paginate = array(
//ここで指定しないと初期値の"20"が適用されてしまう。
'limit' => 4,

//結合側で指定してもよい。
'order' => array( 'Post.id' => 'desc' ),

//カラム指定はここで行う。
'fields' => array('Post.id', 'Post.name', 'Post.created', 'Post.modified'),

'joins' => array(
array(
'type' => 'INNER',
'alias' => 'Sub',
//ここに複雑な生SQLを記述する。
//カッコ()で包むことを忘れずに。
//なお、ここでのカラム指定は無視される。
//'fields'で行うこと。
'table' => "(SELECT id FROM posts WHERE name LIKE '%hoge\_%')",
'conditions' => array('Post.id = Sub.id')
)
)
);
$this->set('posts', $this->paginate());

生成されるSQL
レコード取得用

SELECT `Post`.`id`, `Post`.`name`, `Post`.`created`, `Post`.`modified`
FROM `caketest`.`posts` AS `Post`
INNER JOIN (SELECT id FROM posts WHERE name LIKE '%hoge\_%') AS `Sub`
ON (`Post`.`id` = `Sub`.`id`)
WHERE 1 = 1
ORDER BY `Post`.`id` desc
LIMIT 4
件数取得用

SELECT COUNT(*) AS `count`
FROM `caketest`.`posts` AS `Post`
INNER JOIN (SELECT id FROM posts WHERE name LIKE '%hoge\_%' ORDER BY id) AS `Sub`
ON (`Post`.`id` = `Sub`.`id`)
WHERE 1 = 1