CakePHP 3のQueryBuilderの使い方をまとめてみた！

こんにちはPHP Matsuriでは色々とお世話になってる@kozoです。
この記事は2014年CakePHP Advent Calendarの11日目です

CakePHP 3系からモデルは大きく変わり、戻り値が配列からオブジェクトになるとか、Modelが1ファイルだったものがTableとEntityに分かれるなど色々変わって非常に便利になってます！

今回は大きく変わったもう1個のfind(SELECT句)のSQLの組み立て方法について調べてみました。

find

QueryBuilderの開始地点になり、SELECT用のQueryオブジェクトが生成されます。

QueryオブジェクトをメソッドチェーンでつなげることでSQLを組み立てます。

// 基本的な使い方
$this->Users->find()
    ->where(条件A)
    ->where(条件B)
    ->order(ソート条件A)
    ->order(ソート条件B)
    ->select(取得フィールド)
    ->all()

where

WHERE句を生成します。

where以外にもandWhere, orWhereメソッドも存在します。

基本的な使い方

$this->Users->find()
     ->where(['Users.sex' => 1, 'Users.name LIKE' => '%太郎%']);

// SQL
WHERE "Users"."sex" = 1 AND "Users"."name" LIKE '%太郎%'

メソッドチェーン(上記配列と同じ結果を返す)

$this->Users->find()
     ->where(['Users.sex' => 1])
     ->where(['Users.name LIKE' => '%太郎%']);

// SQL
WHERE "Users"."sex" = 1 AND "Users"."name" LIKE '%太郎%'

OR句

$this->Users->find()
     ->where(['Users.sex' => 1, 
             'OR' => [['Users.name LIKE' => '%太郎%'], ['Users.name LIKE' => '%花子%']]]);

// SQL
WHERE ("Users"."sex" = 1 AND ("Users"."name" like '%太郎%' OR "Users"."name" like '%花子%')

order

order句を生成します

$this->Users->find()
    ->where(['Users.name' => '太郎'])
    ->order(['Users.name' => 'ASC'])
    ->order(['Users.id' => 'DESC']);

// SQL
WHERE "Users"."name" = '太郎' ORDER BY "Users"."name" ASC, "Users"."id" DESC

group, having

GROUP BY, HAVINGを作成します。

$this->Users->find()
    ->group(['Users.account_id'])
    ->having(['Users.account_id >' => 100])

// SQL
GROUP BY "Users"."account_id"  HAVING "Users"."account_id" > 100

limit, offset

LIMIT, OFFSET句を生成します。

$this->Users->find()
    ->limit(100)
    ->offset(10)

// SQL
LIMIT 100 OFFSET 10

all, first, toArray

where, order句等をチェーンでつなげてもSQLを組み立てようとしただけで、実際のSQLはまだ発行されていない状態です。

SQLを実行する為にはall, first, toArrayのメソッドを実行する必要があります。

メソッドの違い

メソッド	詳細
all	SQLを実行して`Iterator`を実装した`ResutSetクラス`を戻します。 Iteratorでループしたい場合はこれを利用します
first	SQLを実行して`Entityクラス`を`1件`戻します
toArray	SQLを実行して`Entityクラス`の`配列`を戻します

※明示的にメソッド実行しなくても、SQLを組み立てているQueryオブジェクトに対してforeachでループするタイミングでもSQLが実行されます。

contain

relation先のテーブルのデータを一緒に取得します。

$this->Users->find()
    ->contain(['Address']);

newExpr

newExprはQueryExpressionオブジェクトを生成します。

newExprを使うことで複雑なSQLを組み立てやすくなります。

※QueryExpressionクラスには、or_, and_, eq, notEq, gt, lt, gte, lte, isNull, isNotNull, like, notLike, in, notIn, between, notと一通りそろっています。

$or = $query->newExpr()->or_(['Users.name LIKE' => '%次郎%']);
$and = $query->newExpr()->add([['Users.name LIKE' => '%山田%'], ['Users.name LIKE' => '%太郎%']]);
$or->add($and);
$query = $this->Users->find();
$result = $query
    ->where(['Users.id' => 5])
    ->orWhere($or)
    ->all();

// SQL
WHERE (("Users"."name" like '%次郎%' OR ("Users"."name" like '%山田%' AND "Users"."name" like '%太郎%')) OR "Users"."id" = 5)

クロージャーを使うことでもQueryExpressionを利用して同じSQLを発行することが出来ます。

$result = $this->Users->find()
    ->where(['Users.id'=>5])
    ->orWhere(function ($exp, $query) {
        $or = $exp->or_([['Users.name LIKE' => '%次郎%']]);
        $and = $exp->and_([['Users.name LIKE' => '%山田%'], ['Users.name LIKE' => '%太郎%']]);
        return $or->add($and);
    })
    ->all();

// SQL
WHERE (("Users"."name" like '%次郎%' OR ("Users"."name" like '%山田%' AND "Users"."name" like '%太郎%')) OR "Users"."id" = 5)

その他

メソッド名	概要
get	プライマリーの値指定で`Entity`を取得します(※Tableクラスのメソッド)
count	COUNTのSQLを発行します
hydrate	falseを指定することで、Entityで取得するデータの形式を`配列`で取得することが出来ます
union, unionAll	UNION句を生成します
query	Queryオブジェクトを生成します(※Tableクラスのメソッド)
insert	INSERT句を生成します(※saveメソッドは別途あり)
update	INSERT句を生成します(※saveメソッドは別途あり)
delete	DELETE句を生成します(※deleteメソッドは別途あり)