Edited at
CakePHPDay 11

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

More than 3 years have passed since last update.


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