CakePHP4 クエリビルダー 記法まとめ

概要

クエリビルダーの使い方をまとめる。非推奨な方法が混じっているかもしれない。
詳しくはCookbookを読むこと。

命名は規約に則っているものとします。
実際のDBのテーブル名はスネークケース、CakePHPで使用するモデル名はパスカルケースで記述しています。

使用している CakePHP のバージョンは 4.3.6

まずはクエリを発行してみよう

とりあえずSQLを発行できなければ始まらない。そのための準備

use Cake\ORM\TableRegistry;

// "users"テーブルを使いたいので、UsersTableクラスのインスタンスを取得
// @var \App\Model\Table\UsersTable
$users = TableRegistry::getTableLocator()->get('Users');

クエリビルダーを使用してクエリを組み立てる準備を開始します。

// クエリの開始
$query = $users->find();

これはほぼSELECT * FROM usersです。この$queryにSELECT句やWHERE句などを付与していきます。

$query = $users->find()
       ->select(['id', 'name'])
       ->where(['id >' => 1, 'name LIKE' => '田中%', 'age' => 20])
       ->order(['id', 'name'])
       ->group(['id', 'name']);

結果の取得

find()はQueryオブジェクトを生成するものであり、Queryオブジェクトでselectやwhereをいくらチェーンしても実行はされません。
完成した（あるいは途中の）SQLの発行は、次のいずれかが起こるまで実行されません。

全取得

$resultSet = $query->all(); // ResultSet
$list = $query->toList(); // 配列
$arr = $query->toArray(); // 配列？

toList と toArray の違いがわからなかったので、以下ソースから説明文を引用します。

vendor/cakephp/cakephp/src/ORM/Query.php

// toArray() Returns a key-value array with the results of this query.
// toList() Returns a numerically indexed array with the results of this query.

EntityクラスにもtoArrayがあるので、foreachしてから中身をtoArrayすればkey => value で取得できたのですが
初めからデータを配列で取得したい場合は以下のように記述します。

$result = $articles->find()->enableHydration(false)->toList();

最初の行を取得

データを一行のみ取得したい場合があると思います。その場合はfirst()を使います。

$row = $query->first();
$row = $query->firstOrFail(); // 取得できない場合は例外

クエリをイテレートする

foreach ($query as $row) {
    debug($row);
}

all()などで結果を取得していない場合でもforeachでイテレートすることは可能です。
ただし、このタイミングでSQLが実行されることに注意してください。

クエリの遅延評価についてはCookbookを参照します。

例えばQueryのままViewに値を渡してforeachして、もしアソシエーション等に誤りがあってエラーが出た場合、エラー箇所がforeachの場所になってしまい、ソースのどこで取得したデータなのかがエラーに残りません。

指定したカラムの値リスト

特定のカラムのみの配列を単純に取得したい場合があるかもしれません。
その場合はextractが使えます。戻り値はコレクションであり、イテレータとして使用できます。

// @var Cake\Collection\Iterator\ExtractIterator
$name = $query->extract('name');

件数の取得

件数を取得する場合はcountを使用します。

$count = $query->count();
$count = $query->all()->count(); // all実行後のResultSetでも可

// 配列の場合
$list = $query->toList();
$count = count($list);

発行するSQLの確認

発行されるSQLの内容を確認したい場合はsqlを使用します。

$sql = $query->sql();

SELECT句の指定

SELECT句の指定方法について記述します。
SELECT句の指定がない場合、結合したテーブルも含めた全カラムを取得します。
仮にカラム名が被っていたとしても問題なく、すべては{テーブル名またはエイリアス}__{カラム名}で取得されることに注意して下さい。

以下、別名は必要なければ記述を省略しています。
「*」も実際には使用されませんが、全カラムを取得する場合は疑似的に記述します。

selectに指定できるのは、string、単純な配列、Table、Association、またはExpressionInterface実装クラスです。

単一指定 (string)

$query->select('id');
$query->select('name'); // 何度も呼ばれる場合は追加される
// => SELECT id, name FROM users 

// リセットしたい場合は第二引数をtrueにする(overwrite)
$query->select('age', true);
// => SELECT age FROM users 

配列で指定 (array)

$query->select(['id', 'name', 'address']);
// => SELECT id, name, address FROM users

別名を指定することもできます。

// 別名を指定
$query->select(['namae' => 'name', 'juusyo' => 'address']);
// => SELECT name AS namae, address AS juusyo FROM users

全カラムの取得 (Table)

全カラムを取得したい場合は次のように指定する。

$query->select($users);
// => SELECT users.{各カラム}, ... FROM users

結合テーブルの場合 (Association)

$query->contain(['items']); // テーブルの結合（後述する）
$query->select($users->items);
// => SELECT items.{各カラム}, ... FROM users

CASE文 (ExpressionInterface)

CookbookにはaddCaseで例文が載っているけど、次のように言われます （の割に使い方は載ってない）
@deprecated 4.3.0 — Use QueryExpression::case() or CaseStatementExpression instead

caseでは以下のように記述できます。

// @var CaseStatementExpression
$case = $query->newExpr()
		    ->case($query->identifier('user.age'))
		    ->when(true)->then('Yes')
		    ->when(false)->then('No')
		    ->else('Maybe');
$query->select($case)
// => (CASE user.age WHEN true THEN 'Yes' WHEN false THEN 'No' ELSE 'Maybe' END)

$case = $query->newExpr()
		    ->case()
		    ->when($query->newExpr()->add(['user.age >=' => 10]))->then('10')
		    ->when($query->newExpr()->add(['user.age >=' => 20]))->then('20')
		    ->else('0');
$query->select($case)
// => (CASE WHEN user.age >= 10 THEN '10' WHEN user.age >= 20 THEN '20' ELSE '0' END )

SELECTの注意点

以下のようにすべてまとめるのはNG

$query->select(['id', 'name', $users, $users->items, $case]);

分けて記述する

$query->select(['id', 'name']) // これは配列
      ->select($users)         // これはTable
      ->select($users->items)  // これはAssociation
      ->select($case);         // これはExpression

SQL関数 (MAXやMINなど)

SQL関数を使うにはfunc()を使用します。4.3.6時点で確認できるSQL関数は以下です。

RAND、SUM、AVG、MAX、MIN、COUNT、CONCAT、COALESCE、CAST、DATEDIFF、EXTRACT、DATE_ADD、DAYOFWEEK、NOW、CURRENT_DATE、CURRENT_TIME、ROW_NUMBER、LAG、LEAD

その他（GROUP_CONCATなど）についてもメソッドとして呼び出すことは可能ですが、
その場合はこちらの記事を見た方が安心できると思います。

以下のように使います。（例）

$concat = $query->func()->concat([
    'users.name' => 'identifier', // フィールドの識別
    '123' => 'literal', // リテラルな値
    '456'
]);
// => CONCAT(users.name, 123, :c0)

$count = $query->func()->count('*'); // COUNT(*)

$query->select(['count' => $count]);
$query->select($concat);
$query->order($concat);

nowは以下のように指定することができます。

$now = $query->func()->now(); // NOW()
$now = $query->func()->now('datetime'); // NOW()
$current_date = $query->func()->now('date'); // CURRENT_DATE()
$current_time = $query->func()->now('time'); //CURRENT_TIME()

WHERE句の指定

大抵の場合は単純に書くことが可能です。

$query->where(['id' => 1]); // id = ?
$query->where(['id >' => 1]); // id > ?
$query->where(['id >=' => 1]); // id >= ?
$query->where(['id !=' => 1]); // id != ?
$query->where(['name LIKE' => '田中%']); // name LIKE '田中%'

勿論まとめて配列で渡すことも可能です。
where句は通常ANDで結合されます。

$query->where(['id' => 1, 'name LIKE' => '田中%']); // id = 1 AND name LIKE '田中%'

OR結合したい場合は以下のように書くことが可能です。

$query->where(['OR' => ['name'=>'田中', 'kana'=>'田中']]); // (name = '田中' OR kana = 'たなか')
$query->where(['OR' => [['id'=>'1'],['id'=>2]]]); // (id = 1 OR id = 2)

// この2行が実行された場合は以下のようにANDで結合されます。
// (name = '田中' OR kana = 'たなか') AND (id = 1 OR id = 2)

whereにはQueryExpressionを指定することも可能です。Cookbook を参照。

ORDER BY句の指定

// order(): string, array, またはExpression
$query->order('id'); // 単一指定
$query->order(['id', 'name']); // 配列指定
$query->order(['id' => 'ASC', 'name' => 'DESC']); // 昇順・降順を指定

// orderAsc(), orderDesc(): string または Expression。配列は不可
$query->orderAsc('id');
$query->orderDesc('name');

GROUP BY句, HAVING句の指定

$query->group('id'); // 単一指定
$query->group(['id', 'name']); // 配列指定

$query->having(['count > ' => 3]); // HAVING

LIMIT句, OFFSET句の指定

$query->limit(100);
$query->offset(200);
// => LIMIT 100 OFFSET 200

もしかしたらpage()の方がわかりやすい場面もあるかもしれません。

$query->limit(100);
$query->page(3);
// => LIMIT 100 OFFSET 200

LIMITはデータの制限（取得数）です。
OFFSETは「取得開始の位置」と言われることもあるのですが、OFFSET(200)の場合の開始位置は201なので、「取得しないデータ数」ととらえた方がわかりやすい気がします。

膨大なデータの一覧表のようにデータを分割する場合、LIMITを分割数として、OFFSETは選択したページとLIMITから計算するものなので、「limit()で分割して、page()でページを指定する」はだいぶ直感的でわかりやすく助かります。
といっても offset = (page - 1) * limit 程度ですが。
実際、内部的にやってることは単に({ページ番号} - 1) * {limit()で設定した値}でoffset()を実行しているだけです。

JOIN句

関連付くデータを contain() でロードすることもできますが、 追加の join をクエリービルダーに加えることもできます。
アソシエーションについては今回記述しません。また改めてまとめたいと思います。

// 単一指定
$query->join([
        'table' => 'comments',
        'alias' => 'c',
        'type' => 'LEFT',
        'conditions' => 'c.article_id = articles.id',
    ]);

// 複数指定
$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => 'c.article_id = articles.id',
        ],
        'u' => [
            'table' => 'users',
            'type' => 'INNER',
            'conditions' => 'u.id = articles.user_id',
        ]
    ]);