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

## 概要

クエリビルダーの使い方をまとめる。非推奨な方法が混じっているかもしれない。
詳しくは[Cookbook](https://book.cakephp.org/4/ja/orm/query-builder.html)を読むこと。

命名は[規約](https://book.cakephp.org/4/ja/intro/conventions.html#id4)に則っているものとします。
実際のDBのテーブル名はスネークケース、CakePHPで使用するモデル名はパスカルケースで記述しています。

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

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

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

```php
use Cake\ORM\TableRegistry;

// "users"テーブルを使いたいので、UsersTableクラスのインスタンスを取得
// @var \App\Model\Table\UsersTable
$users = TableRegistry::getTableLocator()->get('Users');
```
クエリビルダーを使用してクエリを組み立てる準備を開始します。
```php
// クエリの開始
$query = $users->find();
```
これはほぼ```SELECT * FROM users```です。この```$query```にSELECT句やWHERE句などを付与していきます。
```php
$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の発行は、次のいずれかが起こるまで実行されません。

#### 全取得
```php
$resultSet = $query->all(); // ResultSet
$list = $query->toList(); // 配列
$arr = $query->toArray(); // 配列？
```
toList と toArray の違いがわからなかったので、以下ソースから説明文を引用します。
```php: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 で取得できたのですが
初めからデータを配列で取得したい場合は以下のように記述します。
```php
$result = $articles->find()->enableHydration(false)->toList();
```

#### 最初の行を取得

データを一行のみ取得したい場合があると思います。その場合は```first()```を使います。
```php
$row = $query->first();
$row = $query->firstOrFail(); // 取得できない場合は例外
```
#### クエリをイテレートする
```php
foreach ($query as $row) {
    debug($row);
}
```
```all()```などで結果を取得していない場合でもforeachでイテレートすることは可能です。
ただし、このタイミングでSQLが実行されることに注意してください。

クエリの遅延評価については[Cookbook](https://book.cakephp.org/4/ja/orm/query-builder.html#id6)を参照します。

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

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

特定のカラムのみの配列を単純に取得したい場合があるかもしれません。
その場合は```extract```が使えます。戻り値はコレクションであり、イテレータとして使用できます。
```php
// @var Cake\Collection\Iterator\ExtractIterator
$name = $query->extract('name');
```

#### 件数の取得
件数を取得する場合は```count```を使用します。
```php
$count = $query->count();
$count = $query->all()->count(); // all実行後のResultSetでも可

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

#### 発行するSQLの確認
発行されるSQLの内容を確認したい場合は```sql```を使用します。
```php
$sql = $query->sql();
```

## SELECT句の指定

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

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

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

#### 単一指定 (string)

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

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

#### 配列で指定 (array)

```php
$query->select(['id', 'name', 'address']);
// => SELECT id, name, address FROM users
```
別名を指定することもできます。

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


#### 全カラムの取得 (Table)

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

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

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

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

#### CASE文 (ExpressionInterface)

CookbookにはaddCaseで[例文](https://book.cakephp.org/4/ja/orm/query-builder.html#case)が載っているけど、次のように言われます （の割に使い方は載ってない）
```@deprecated 4.3.0 — Use QueryExpression::case() or CaseStatementExpression instead```

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

```php
// @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
```php
$query->select(['id', 'name', $users, $users->items, $case]);
```
分けて記述する
```php
$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```など）についてもメソッドとして呼び出すことは可能ですが、
その場合はこちらの記事を見た方が安心できると思います。

https://qiita.com/mamy1326/items/e034320ce4885d4bb924

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

```php
$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は以下のように指定することができます。

```php
$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句の指定

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

```php
$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で結合されます。

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

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

```php
$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](https://book.cakephp.org/4/ja/orm/query-builder.html#advanced-query-conditions) を参照。


## ORDER BY句の指定

```php
// 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句の指定

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

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

## LIMIT句, OFFSET句の指定

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

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

```php
$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``` をクエリービルダーに加えることもできます。
アソシエーションについては今回記述しません。また改めてまとめたいと思います。

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

```php
// 複数指定
$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',
        ]
    ]);
```