cakephp3クエリービルダー
条件
テーブルを作成してModelのTableも用意しておく
CREATE TABLE members ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT, tel VARCHAR(15), created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME DEFAULT NULL );
環境はshell用のプログラムのものより一部抜いています
基本
<?php $members = TableRegistry::get('Members'); $query = $members->query(); var_dump( $query->select()->sql() );
SQLを出力して確認
SELECT Members.id AS `Members__id` , Members.name AS `Members__name` , Members.age AS `Members__age` , Members.tel AS `Members__tel` , Members.created AS `Members__created` , Members.modified AS `Members__modified` FROM members AS Members
※基本、selectのみの場合は全部出ます。
※sqlメソッドでクエリービルダーで生成したsqlを出力します。
フィールドを指定
<?php $members = TableRegistry::get('Members'); $query = $members->query(); var_dump( $query->select([ 'aaaa' => 'Members.age' ], true)->sql() , $query->select([ 'count' => 'count(*)' ], true)->sql() );
結果
SELECT Members.age AS `aaaa` FROM members AS Members SELECT count(*) AS `count` FROM members AS Members
配列のkeyが別名、valueが実際の値や条件となる
select
引数が未指定の場合はデフォルトで全てを表示する
第１引数：配列、連想配列でフィールドを指定
第２引数：上書きフラグでtrueで第１引数のもののみ出力（デフォルトはfalse）
未指定の場合は対象のデータを生成
// default
var_dump(
$query->select()->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members
第２引数をtrueにした場合は第１引数のみ表示
var_dump(
$query->select([
'aaaa' => 'Members.age'
], true)->sql()
);
↓
SELECT Members.age AS `aaaa` FROM members AS Members
from
Fromを指定できる
通常は作成したモデルなどを使用するので不要と思われるが独自に別途テーブルが必要な場合など
fromを使用する場合はテーブルのリストを設定する
var_dump(
$query->select()->from(['aaa'])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM aaa
※fromを指定した場合は先にselectも指定しておく必要があります。
where
where句より条件を生成する
select句を記載してから生成
var_dump(
$query->select()->where(['id' => '1111'])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (id = :c0 AND created >= :c1)
複数の条件を指定
var_dump(
$query->select()->where(['id' => '1111', 'created >=' => '2014-01-01'])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (id = :c0 AND created >= :c1)"
条件をいろいろ付与
var_dump(
$query->select()->where([
'and' => [
'id' => '1111',
'created >=' => '2014-01-01',
],
'or' => [
'name' => ['ccc','ddd'],
'id' => 'ddd'
]
])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE ((id = :c0 AND created >= :c1) AND (name = :c2 OR id = :c3))
※通常はandで連結するけど、orを組み込むことも可能
and
andwhereで別途and条件を付与可能
var_dump(
$query->select()->andwhere([
'id' => '10'
])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE id = :c0
※whereを未指定でも問題ない
andwhereを連続で指定
var_dump(
$query->select()->andwhere([
'id' => '10'
])->andWhere([
'name' => 'aaa'
])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (id = :c0 AND name = :c1)
or
orwhereも基本はandwhereと同じ
var_dump(
$query->select()->orWhere([
'id' => 'aaa'
])->orWhere([
'id' => 'bbb'
])->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (id = :c0 OR id = :c1)
whereでfunction指定？
関数を指定してorやnotなどを指定できる
where句のあとにandwhereなどで関数を呼び出すことも可能
var_dump(
$query->select()->where(['name' => 'aaa'])->andWhere(function($exp){
return $exp->or_([
['id' => '10'],
['id' => '20']]);
})->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (name = :c0 AND (id = :c1 OR id = :c2))
inなどを指定したい場合
var_dump(
$query->select()->where(['name' => 'aaa'])->andWhere(function($exp){
return $exp->in(
'id', ['10', '20']
);
})->sql()
);
↓
SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (name = :c0 AND id IN (:c1,:c2))
