RitoLabo

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

  • 公開:
  • 更新:
  • カテゴリ: PHP CakePHP
  • タグ: PHP,CakePHP,3.5,QueryBuilder,QueryExpression,ConnectionManager

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

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

アジェンダ
  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関数を使ったクエリパターン
      1. count()
      2. sum()
      3. avg()
      4. max()
      5. concat()
      6. dateDiff()
      7. now()
      8. date_format()
  5. WHERE
    1. 基本型
    2. OR
    3. orWhere()
  6. クロージャを用いたWHERE句の組み立て(QueryExpressionクラス)
    1. 基本形
    2. or_()とand_()
      1. or_()
      2. and_()
    3. SQL関数を使ったクエリパターン
    4. 条件生成のメソッド
      1. eq()
      2. notEq()
      3. like()
      4. notLike()
      5. in()
      6. notIn()
      7. gt()
      8. gte()
      9. lt()
      10. lte()
      11. isNull()
      12. isNotNull()
      13. between()
  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()メソッドで取得するということは「最初の1件のみ」を取得するということになりますが、もし戻り値が0件であった場合に、このメソッドの場合は例外を発生させられます。

$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()を使うと、1組の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件を1ページとして、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);
}