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

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」といったようにエイリアスで取得名を変更します。

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() を使います。

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