CakePHP3でのクエリビルダ記法まとめ[QueryBuilder/QueryExpression]

CakePHP3 のクエリビルダーの記法をここにまとめています。随時更新中。

CakePHP3 はバージョンによって多少の違いがあります。ここに記載されているのは CakePHP3.5 時点での記法です。

Contents

1. クエリビルダについて
2. クエリビルダ基本形
3. 結果データ取得
   1. all()
   2. toArray()
   3. first()
   4. firstOrFail()
   5. count()
   6. sql()
   7. extract()
4. SELECT
   1. 基本型
   2. エイリアス
   3. Case 文
   4. SQL関数を使ったクエリパターン
5. WHERE
   1. 基本型
   2. OR
   3. orWhere()
6. クロージャを用いた WHERE 句の組み立て（QueryExpression クラス）
   1. 基本形
   2. or_() と and_()
   3. SQL関数を使ったクエリパターン
   4. 条件生成のメソッド
7. ORDER
8. GROUP BY と HAVING
9. LIMIT
10. OFFSET と page
11. JOIN
12. 直接 SQL 文を記述する（ConnectionManager クラス）

クエリビルダについて

[公式]CakePHP3 クエリービルダー
https://book.cakephp.org/3.0/ja/orm/query-builder.html

CakePHP3 のクエリビルダは裏側に PDO プリペアードステートメントを使っているので、基本的に SQL インジェクション攻撃からは守られています。

クエリビルダ基本形

<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\ORM\TableRegistry;   // ① TableRegistry を use する

class SampleController extends AppController
{
  public function index()
  {
    // テーブルクラスのインスタンスを取得
    $users = TableRegistry::get('Users');
    // クエリ開始
    $users = $users->find();
  }
}

1. TableRegistry クラスを use します。
2. テーブルクラスのインスタンスを取得。 get() メソッドで渡している引数はテーブル名
3. クエリを開始します。ここに条件式などを追加し、取得を行います。

結果データ取得

SQL 文を組み立てた後にそれらを実行し結果データを取得するわけですが、CakePHP3 のクエリビルダにはいくつかの取得メソッドがあります。

all()

Iterator を実装した ResutSet クラスで取得します。

$users = $users->find()->all();

toArray()

Entity クラスの配列を取得します。

$users = $users->find()->toArray();

first()

Entity クラスを最初の 1 件のみ取得します。結果が複数あったとしても、取得するのは 1 件のみです。

$users = $users->find()->first();

firstOrFail()

first() メソッドの例外付きバージョンです。 first() メソッドで取得するということは「最初の１件のみ」を取得するということになりますが、もし戻り値が０件であった場合に、このメソッドの場合は例外を発生させられます。

$users = $users->find()->firstOrFail();

count()

結果の合計件数を取得します。 count() メソッドの場合は、limit ・ offset ・ page 句は無視されます。

$users = $users->find()->count();

sql()

結果データではありませんが、組み立て発行しようとしている SQL 文を取得する事ができます。

$users = $users->find()
    ->select(['id', 'name'])
    ->where(['role' => 1])
    ->sql();

// [SQL] => SELECT Users.id AS `Users__id`, Users.name AS `Users__name` FROM users Users WHERE role = :c0

出力例を見るとわかる通り、PDO プリペアドステートメントで問い合わせ値をバインドする前の SQL 文が出力される為、値までは入ってきません。

この例でいうと、role の「1 」という値が「:c0 」になっているのがわかります。

extract()

カラムから値リストを取得します。引数にカラム名を渡す事で、そのカラムのみを取得します。

$users = $users->find()->extract('name');

例えば以下のようにした場合、

$users = TableRegistry::get('Users');
$users = $users->find()->extract('name');

$ret = array();
foreach ($users as $user) {
    $ret[] = $user;
}

print_r($ret);

変数 $ret の中はこのようになります。

Array
(
    [0] => test01
    [1] => test02
    [2] => test03
    [3] => test04
    [4] => test05
    [5] => test06
    [6] => test07
        .
        .
        .
)

指定した name カラムのみを取得した形になります。

SELECT

ここからは SELECT での記法を記します。

基本型

$users = TableRegistry::get('Users');
$users = $users->find()
    ->select(['id', 'name', 'email'])
    ->all();

SELECT を書く場合は select() メソッドを用いて記述します。 SELECT するカラムが複数ある場合は、上記のように配列の形で渡します。

エイリアス

エイリアスは連想配列でセットします。 KEY部分にはエイリアス名を、VALUE部分にカラム名を記述します。

$users = $users->find()
    ->select(['user_id' => 'id', 'user_name' => 'name', 'mailaddress' => 'email'])
    ->all();

'Alias' => 'Column name'

Case 文

Case 文を書く場合には、newExpr() メソッドで定義していきます。

$users = TableRegistry::get('Users');
$users = $users->find();

// Case文作成 - 権限値 = 1
$role_sys = $users->newExpr()
  ->addCase(
    $users->newExpr()->add(['role']),
    1,
    'integer'
  );

// Case文作成 - 権限値 = 2
$role_admin = $users->newExpr()
  ->addCase(
    $users->newExpr()->add(['role']),
    2,
    'integer'
  );

$users->select([
  'role_sys' => $users->func()->sum($role_sys),
  'role_admin' => $users->func()->sum($role_admin),
])
->all();

// [出力] => SELECT (SUM(CASE WHEN role THEN 1 END)) AS `role_sys`, (SUM(CASE WHEN role THEN 2 END)) AS `role_admin` FROM users

上記例では、権限カラムの値が 1 であるレコードと 2 であるレコードのそれぞれの集計を行っています。

SQL関数を使ったクエリパターン

SELECT の中では、count() や sum() などの集計系や concat() などの結合系、now() などの時間系も取扱いますが、それらの記法を以下に記します。

count()

結果レコードの総件数を取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
$users->select(['id' =>  $users->func()->count('id')])
      ->sql();

// [SQL] => SELECT (COUNT(id)) AS `id` FROM users

sum()

対象カラムの和（合計）を取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
$users
  ->select(['id' =>  $users->func()->sum('id')])
->all();

// [SQL] => SELECT (SUM(id)) AS `id` FROM users

avg()

対象カラムの平均値を取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
$users->select(['role_avg' =>  $users->func()->avg('role')])
      ->all();

// [SQL] => SELECT (AVG(role)) AS `role_avg` FROM users

max()

対象カラムの最大値（対象範囲レコード内で最も大きい数値）を取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
$users->select(['role_max' =>  $users->func()->max('role')])
      ->all();

// [SQL] => SELECT (MAX(role)) AS `role_max` FROM users

concat()

カラム値同志、もしくはカラム値とテキストなどを結合した形で取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
// 「id」と「name」を「:（コロン）」でつなげて「id : name」の形にする
$concat = $users->func()->concat([
  'id' => 'identifier',
  ' : ',
  'name' => 'literal'
]);
$users
  ->select(['name' =>  $concat])
  ->sql();

// [SQL] => SELECT (CONCAT(id, ":", name)) AS `name` FROM users

上記例の場合、name カラムは「1 : 名前」「2 : 名前」の形になります。

dateDiff()

二つの指定した日時の差を取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
// レコードの作成日と2018/01/01との日付差を取得する
$target_date = "'2018-01-01'";
$dateDiff = $users->func()->dateDiff([
  'created' => 'identifier',
  $target_date => 'literal',
]);
$users
  ->select(['created_diff' => $dateDiff])
  ->all();

// [SQL] => SELECT (DATEDIFF(created, '2018-01-01')) AS `created_diff` FROM users

now()

現在日時を取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
$now = $users->func()->now();
$users
  ->select(['now' => $now])
  ->sql();

// [SQL] => SELECT (NOW()) AS `now` FROM users

上記例のようにしてはあまり取得はしませんが、ループで回す結果データに対して日時を表示させる場合は以下のようになります。

foreach ($users as $user) {
  // now() で取得した日時データを表示（取得）する
  echo $user->now->i18nFormat('YYYY/MM/dd HH:mm:ss');

  // [出力] => 2018/01/1 12:00:00
}

date_format()

datetime 型などの日時形式のカラムに対して、出力フォーマットを指定した形で取得します。

$users = TableRegistry::get('Users');
$users = $users->find();
// createdカラムを「YYYY.MM.dd」の形で取得する
$date_format = $users->func()->date_format([
  'created' => 'identifier',
  '"%Y.%m.%d"' => 'identifier'
]);
$users
  ->select(['created_ymd' =>  $date_format])
  ->all();

// [SQL] => SELECT (date_format(created, "%Y.%m.%d")) AS `created_ymd` FROM users

ここで一点注意なのが、基のカラムと同じ名前で取得しようとすると、結果オブジェクトには代入できないため変形後の値が入らず取得できません。 なので「created 」を「created_ymd 」といったようにエイリアスで取得名を変更します。

WHERE

ここからは WHERE 句での記法を記します。

基本型

$users = TableRegistry::get('Users');
$users = $users->find()->where(['id ' => 1])->all();

連想配列、もしくはメソッドチェーンの形で複数の WHERE 句を追加できます。

またその場合、クエリは全て「AND 」でつながります。

連想配列パターン

// 連想配列パターン
->where(['id ' => 1, 'role < ' => 3])

// [SQL] => WHERE `id` = 1 AND `role` < 3

メソッドチェーンパターン

// メソッドチェーンパターン
->where(['id ' => 1])
->where(['role < ' => 3])

// [SQL] => WHERE `id` = 1 AND `role` < 3

OR

where() メソッド内の連想配列の中で、OR識別子を使う事でネストされた OR 文を挿入できます。

ネストされた OR パターン（ID が 1 以上で、権限が 1 もしくは 3）

$users = TableRegistry::get('Users');
$users = $users->find()
                // ネストされた OR パターン（ID が 1 以上で、権限が 1 もしくは 3）
               ->where([
                    'id >=' => 1,
                    'OR' => [
                        ['role' => 1],
                        ['role' => 3]
                    ]
               ])->all();

// [SQL] => WHERE `id` >= 1 AND ( `role` = 1 OR `role` = 3 )

orWhere()

orWhere() と where() を使うと、１組の OR 条件を組み立てられます。

1 組みの OR パターン（ID が 1 以上もしくは 10 以下）

$users = $users->find()
                // 1 組みの OR パターン（ID が 1 以上もしくは 10 以下）
                ->where(['id >=' => 1])
                ->orWhere(['id <=' => 10])
                ->all();

// [SQL] => WHERE (`id` >= 1 OR `id` <= 10 )

もちろん、その後に where() でチェーンすれば AND でつながっていきます。

1 組みの OR と AND パターン（ID が 1 以上もしくは 10 以下であり、権限値が 1 のもの）

$users = $users->find()
                // 1 組みの OR と AND パターン（ID が 1 以上もしくは 10 以下であり、権限値が 1 のもの）
                ->where(['id >=' => 1])
                ->orWhere(['id <=' => 10])
                ->where(['role' => 1])
                ->all();

// [SQL] => WHERE ( (`id` >= 1 OR `id` <= 10 ) AND `role` = 1 )

※しかし残念ながら、orWhere() メソッドは 3.5.0 以降、非推奨になりました。紹介はしましたが、OR は where() メソッドの中で（ネストされた OR パターンを）使いましょうという事です。

クロージャを用いた WHERE 句の組み立て（QueryExpression クラス）

where() をクロージャ（QueryExpression クラス）を用いて定義する事で、より柔軟、かつ詳細な WHERE 句を組み立てる事ができます。

基本形

クロージャでの WHERE パターン（権限値が 1, ID が 0 ではなく、名前に「子」が含まれており「test 」が含まれないもの）

$users = $users->find()
                // クロージャでの WHERE パターン（権限値が 1, ID が 0 ではなく、名前に「子」が含まれており「test 」が含まれないもの）
                ->where(function($exp) {
                    return $exp
                            ->eq('role', '1')
                            ->notEq('id', '0')
                            ->like('name', '%子%')
                            ->notLike('name', '%test%');
                })
                ->all();

// [SQL] => WHERE (role = 1 AND id != 0 AND name LIKE %子% AND name NOT LIKE %test%)

or_() と and_()

クロージャ内で or_() もしくは and_() メソッドを使って組み立てる事で、対象のクエリを先に組み立ててからセットする事が出来ます。

or_()

or_() メソッドを用いることで先に 1 組の OR での WHERE パターンを組み立てる事ができ、それを後から加える事ができます。

クロージャでの or_() を使った WHERE パターン（権限値が 1 もしくは 3 で、名前に「子」が含まれているもの）

$users = $users->find()
               // クロージャでのor_()を使ったWHEREパターン（権限値が1もしくは3で、名前に「子」が含まれているもの）
               ->where(function($exp) {
                 $or_query = $exp->or_(function($or) {
                   return $or->eq('role', 1)
                             ->eq('role', 3);
                 });
                 return $exp
                   ->add($or_query)
                   ->like('name', '%子%');
               })
               ->all();

// [SQL] => WHERE ( (role = 1 OR role = 3) AND name LIKE '%子%' )

また、組み立てたパターンを組み込み際には add() を使います。

and_()

or_() メソッドの逆です。 and_() メソッドを用いることで先に 1 組の AND での WHERE パターンを組み立てる事ができ、それを後から加える事ができます。

クロージャでの or_() を使った WHERE パターン（権限値が 1 と 3 ではなく、名前に「子」が含まれているもの）

$users = $users->find()
               // クロージャでのand_()を使ったWHEREパターン（権限値が1と3ではなく、名前に「子」が含まれているもの）
               ->where(function($exp) {
                 $and_query = $exp->and_(function($or) {
                   return $or->eq('role', 1)
                             ->eq('role', 3);
                 });
                 return $exp
                   ->not($and_query)
                   ->like('name', '%子%');
               })
               ->all();

// [SQL] => WHERE (NOT ((role = 1 AND role = 3)) AND name LIKE '%子%')

上記のように、組み立てたパターンを組み込む際に否定での追加もできます。その場合には not() を使います。

SQL関数を使ったクエリパターン

SQL 文を組み立てる際に、日付操作などの SQL関数を使うこともできます。

SQL関数 YEAR() を使った WHERE パターン（作成日が 2018 年で、権限値が 2 のもの）

$users = $users->find()
               // SQL関数 YEAR() を使ったWHEREパターン（作成日が2018年で、権限値が2のもの）
               ->where(function($exp, $q) {
                 $year = $q->func()->year([
                   'created' => 'identifier'
                 ]);
                 return $exp
                   ->eq($year, 2018)
                   ->eq('role', 2);
               })
               ->all();

// [SQL] => WHERE ( ( year(created) ) = 2018 AND role = 1 )

SQL関数 DATA_FORMAT() を使った WHERE パターン（作成日が 2018 年 1 月と 2 月で、権限値が 2 のもの）

$users = $users->find()
               // SQL関数 DATA_FORMAT() を使ったWHEREパターン（作成日が2018年1月と2月で、権限値が2のもの）
               ->where(function($exp, $q) {
                 $year_month = $q->func()->date_format([
                   'created' => 'identifier',
                   "'%Y-%m'" => 'literal'
                 ]);
                 return $exp
                  ->in($year_month, ['2018-01', '2018-02'])
                   ->eq('role', 2);
               })
               ->all();

// [SQL] => WHERE ( (date_format(created, '%Y-%m')) IN ('2018-01', '2018-02') AND role = 2 )

条件生成のメソッド

クロージャを用いて WHERE 句を組み立てる場合に、以下のようなメソッドが使えます。

eq()

「等しいもの（イコール）」である条件で生成します。

eq() を使ったメソッド例（権限値が 2 のもの）

$users = $users->find()
               // eq() を使ったメソッド例（権限値が2のもの）
               ->where(function($exp) {
                 return $exp->eq('role', 2);
               })
               ->all();

// [SQL] => WHERE ( role = 2 )

notEq()

「等しくないもの（ノットイコール）」である条件で生成します。

notEq() を使ったメソッド例（権限値が 3 ではないもの）

$users = $users->find()
               // notEq() を使ったメソッド例（権限値が3ではないもの）
               ->where(function($exp) {
                 return $exp->notEq('role', 3);
               })
               ->all();

// [SQL] => WHERE ( role != 3 )

like()

LIKE 「含まれる」での条件式を生成します。

like() を使ったメソッド例（名前に「子」が含まれているもの）

$users = $users->find()
               // like() を使ったメソッド例（名前に「子」が含まれているもの）
               ->where(function($exp) {
                 return $exp->like('name', '%子%');
               })
               ->all();

// [SQL] => WHERE `name` LIKE '%子%'

notLike()

NOT LIKE 「含まれない」での条件式を生成します。

notLike() を使ったメソッド例（名前に「子」が含まれていないもの）

$users = $users->find()
               // notLike() を使ったメソッド例（名前に「子」が含まれていないもの）
               ->where(function($exp) {
                 return $exp->notLike('name', '%子%');
               })
               ->sql();

// [SQL] => WHERE `name` NOT LIKE '%子%'

in()

IN 「指定した複数の指定値のいずれかに合致」での条件式を生成します。

in() を使ったメソッド例（権限値が「1 」と「3 」のもの）

$users = $users->find()
               // in() を使ったメソッド例（権限値が「1」と「3」のもの）
               ->where(function($exp) {
                 return $exp->in('role', [1, 3]);
               })
               ->all();

// [SQL] => WHERE role IN (1, 3)

notIn()

NOT IN 「指定した複数の指定値以外」での条件式を生成します。

notIn() を使ったメソッド例（権限値が「1 」と「3 」ではないもの）

$users = $users->find()
               // notIn() を使ったメソッド例（権限値が「1」と「3」ではないもの）
               ->where(function($exp) {
                 return $exp->notIn('role', [1, 3]);
               })
               ->all();

// [SQL] => WHERE role NOT IN (1, 3)

gt()

符号「＞」大なりでの条件式を生成します。

gt() を使ったメソッド例（権限値が 2 より上のもの）

$users = $users->find()
               // gt() を使ったメソッド例（権限値が2より上のもの）
               ->where(function($exp) {
                 return $exp->gt('role', 2);
               })
               ->all();

// [SQL] => WHERE role > 2

gte()

符号「＞＝」大なりイコールでの条件式を生成します。

gte() を使ったメソッド例（権限値が 2 以上のもの）

$users = $users->find()
               // gte() を使ったメソッド例（権限値が2以上のもの）
               ->where(function($exp) {
                 return $exp->gte('role', 2);
               })
               ->all();

// [SQL] => WHERE role >= 2

lt()

符号「＜」小なりでの条件式を生成します。

lt() を使ったメソッド例（権限値が 2 より小さいもの）

$users = $users->find()
               // lt() を使ったメソッド例（権限値が2より小さいもの）
               ->where(function($exp) {
                 return $exp->lt('role', 2);
               })
               ->all();

// [SQL] => WHERE role < 2

lte()

符号「＜＝」小なりイコールでの条件式を生成します。

lte() を使ったメソッド例（権限値が 2 以下のもの）

$users = $users->find()
               // lte() を使ったメソッド例（権限値が2以下のもの）
               ->where(function($exp) {
                 return $exp->lte('role', 2);
               })
               ->all();

// [SQL] => WHERE role <= 2

isNull()

IS NULL 「NULL である」での条件式を生成します。

isNull() を使ったメソッド例（誕生日が NULL のもの）

$users = $users->find()
               // isNull() を使ったメソッド例（誕生日がNULLのもの）
               ->where(function($exp) {
                 return $exp->isNull('birthday');
               })
               ->all();

// [SQL] => WHERE (birthday) IS NULL

isNotNull()

IS NOT NULL 「NULL ではない」での条件式を生成します。

isNotNull() を使ったメソッド例（誕生日が NULL ではないもの）

$users = $users->find()
               // isNotNull() を使ったメソッド例（誕生日がNULLではないもの）
               ->where(function($exp) {
                 return $exp->isNotNull('birthday');
               })
               ->sql();

// [SQL] => WHERE (birthday) IS NOT NULL

between()

BETWEEN（X～ X の間）での条件式を生成します。

between() を使ったメソッド例（権限値が 2 から 4 のもの）

$users = $users->find()
               // between() を使ったメソッド例（権限値が2から4のもの）
               ->where(function($exp) {
                 return $exp->between('role', 2, 4);
               })
               ->all();

// [SQL] => WHERE role BETWEEN 2 AND 4

ORDER

並べ順を指定します。カラムに対して昇順（ASC）もしくは降順（DESC）で指定します。

$users = TableRegistry::get('Users');
$users = $users->find()
               ->order(['id' => 'DESC'])
               ->all();

// [SQL] => SELECT *` FROM users ORDER BY id DESC

複数設定する事も可能です。その場合は配列に追加していきますが、先に記述されているものから優先に効いていきます。

$users = TableRegistry::get('Users');
$users = $users->find()
  ->order(['id' => 'DESC', 'role' => 'ASC'])
  ->sql();

// [SQL] => SELECT *` FROM users ORDER BY id DESC, role ASC

GROUP BY と HAVING

集計などを行う際に集約関数で select している場合にグルービングする GROUP BY やその中での条件付けを行う HAVING は以下のように記述します。

$users = TableRegistry::get('Users');
$users = $users->find();
$users->select([
  'sum' => $users->func()->count('role'),
  'role',
])
->group('role')
->having(['role' => 3])
->all();

// [SQL] => SELECT (COUNT(role)) AS `sum`, role FROM users GROUP BY role  HAVING role = 3

LIMIT

取得件数を制限する LIMIT 句は以下のように記述します。

$users = TableRegistry::get('Users');
$users = $users->find()
  ->limit(100)
  ->all();

// [SQL] => SELECT * FROM users LIMIT 100

OFFSET と page

取得件数を基にしたオフセットを取得するには、offset() を使います。

$users = TableRegistry::get('Users');
$users = $users->find()
  ->limit(10)
  ->offset(30)
  ->all();

// [SQL] => SELECT * FROM users LIMIT 10 OFFSET 30

上記の場合では、「30 件目以降から 10 件を取得」という意味になるので、ID にして 31 から 10 件分（31～ 40）までを取得します。

また、同じようなメソッドで page() がありますが、offset() とは挙動が違います。

$users = TableRegistry::get('Users');
$users = $users->find()
  ->limit(10)
  ->page(30)
  ->all();

// [SQL] => SELECT * FROM users Users LIMIT 10 OFFSET 290

SQL 文を見るとなんとなくわかると思いますが、「limit している 10 件を１ページとして、30 ページ目を取得する。」という意味になり、この場合、ID にして 291 から 10 件分（291～ 300）までを取得します。

JOIN

主たるテーブルから別のテーブルを関連付ける（JOIN）を行う場合は、以下のような記法になります。

$users = TableRegistry::get('Users');
$users = $users->find();
$users
  ->join([
    'table' => 'roles',
    'alias' => 'r',
    'type' => 'INNER', // INNER | LEFT | RIGHT
    'conditions' => 'r.role = Users.role',
  ])
  ->select(['Users.id', 'Users.name', 'role_name' => 'r.name'])
  ->sql();

// [SQL] => SELECT users.id , users.name , r.name AS `role_name` FROM users INNER JOIN roles r ON r.role = users.role

• table
  • JOIN させるテーブル名
• alias
  • JOIN させるテーブル名のエイリアス
• type
  • JOIN のタイプ。「INNER 」「LEFT 」「RIGHT 」で指定します。
• conditions
  • JOIN 元のテーブルと、JOIN させるテーブルの関連付けを指定します。

ただし、この方法は推奨されません。あくまでもクエリビルダのみで JOIN する場合という前提で紹介しています。上記ではなく、モデル同志をアソシエーションを用いて関連付けを行いましょう。

直接 SQL 文を記述する（ConnectionManager クラス）

おまけです。クエリビルダではないですが、ConnectionManager クラスを使って直接 SQL 文を記述し実行する事が出来ます。

<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Datasource\ConnectionManager;

class UsersController extends AppController
{
    public function index()
    {
      $sql = 'SELECT * FROM users';

      $connection = ConnectionManager::get('default');
      $users = $connection->execute($sql)->fetchAll('assoc');

      print_r($users);
    }