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メソッドは別途あり) |