この記事で説明すること/しないこと
- 説明すること
- QueryBuilderで何ができるの？
- SELECT文のみにフォーカスを当てて
- 説明しないこと
- インストール部分
- フレームワークで使えるようにする等の設定
準備するもの
- 適当なPHP MVCフレームワーク・・・・・・・・・1式
- doctrine/dbal ・・・・・・・・・・・・・・・・・1式 (執筆時 version 2.8.0利用）
- 接続用DBとテーブル(今回はMYSQLを使います) ・・1式
- DBのSQLは以下参照
CREATE TABLE `master` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`gender` tinyint(4) DEFAULT NULL,
`type` tinyint(4) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`delete_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ユーザーマスター';
INSERT INTO `master` VALUES
(1,'Oliver',1,1,'2018-10-01 00:00:00','2018-10-05 00:00:00'),
(2,'Sophia',2,2,'2018-10-01 00:00:00',NULL),
(3,'Noah',1,4,'2018-10-01 00:00:00',NULL),
(4,'Olivia',2,5,'2018-10-01 00:00:00',NULL),
(5,'Harry',1,2,'2018-10-01 00:00:00',NULL),
(6,'Jack',1,1,'2018-10-01 00:00:00',NULL),
(7,'Aiden',1,5,'2018-10-01 00:00:00','2018-10-08 00:00:00'),
(8,'Lucas',1,1,'2018-10-01 00:00:00',NULL);
CREATE TABLE `segment` (
`id` int(11) NOT NULL,
`age` tinyint(4) DEFAULT NULL,
`place` varchar(45) DEFAULT NULL,
`marriage` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='セグメントテーブル';
INSERT INTO `segment` VALUES
(1,25,'UK',1),
(2,30,'JP',1),
(3,22,'USA',0),
(4,15,'RUS',0),
(5,14,'CAN',0),
(6,18,'GER',0),
(7,25,'UK',1),
(8,30,'SGP',0);
※DB以外はすべてComposer Installできるので適当にcomposer.jsonに書いてゴリッと入れる
QueryBuilderとは
読んで字の如し。クエリを作る機能です。データベースに問い合わせに行く際に指定が必要な項目を
パーツ毎に分け、論理的且つ合理的にクエリを作ることができるので、大変便利です。
DB検索で検索条件が複数ある場合、条件分岐等でクエリを構築することが容易にできます。
必要最低限な準備として、DBALConnectionで生成したDB接続のインスタンスが必要となります。
※以降便宜上、DBコネクション（$db_connection）として表記します。
DBのコネクションが準備できたら、QueryBuilderオブジェクトを、DBコネクションを引数としてインスタンス化します。
use Doctrine\DBAL\Query\QueryBuilder;
$queryBuilder = new QueryBuilder($db_connection);
インスタンス化したQueryBuilderオブジェクトに対し、以降紹介するメソッドで
条件等をメソッドチェーンを使って記載していく形となります。
またメソッド指定が完了した後、どのようなクエリが作られたかを知りたい場合
echo $queryBuilder->getSQL();
を追記することで生成されたクエリを確認することができます。
適当なクエリは作れない
QueryBuilderをインスタンス化する際に、引数にDBコネクションを設定しているのには理由があります。
それは、本当に存在しているテーブルや、カラム名を使わないと、エラーが出るところ。
単に、仮想のクエリ文字列を作るだけではなく、存在確認などをきちんと行っているので、Typoしても安心です。
エスケープ処理をちゃんとしてくれる
一度は聞いたことがあると思いますが、「SQLインジェクション攻撃」に対しても有効です。
//悪意によって全件取得用のクエリを投入されるケース
$id="null or 1=1";
//クエリビルダでクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> where('m.id = :id')
-> setParameter(':id', $id);
この様に全レコードを取得できるようなコードが埋め込まれたとしても、
setParameterがよろしくやってくれます。
SELECT
全列全件取得
//クエリビルダで全列全件取得クエリを構築する
$queryBuilder -> select('*')
-> from('master', "m");
SELECT * FROM master m
masterテーブルにある全ての列、行を取得することができます。
テーブル名にはエイリアスが設定可能です。
後述のJoinで結合した際、他テーブルに同一カラム名がある場合などを想定し、指定しておくことをお勧めします。
指定列全件取得
//クエリビルダで指定列全件取得クエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m");
SELECT m.id,m.name,m.date FROM master m
masterテーブルにあるid,name ,dateの列、すべての行を取得することができます。
指定列を後から追加
//クエリビルダで指定列全件取得クエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m");
//削除フラグがOnの場合は削除日も合わせて取得する
if($delete_flag === true){
$queryBuilder -> addSelect('m.delete_date');
}
SELECT m.id,m.name,m.date, m.delete_date FROM master m
特定条件のときは、delete_date列を追加で取得する時等、対応が可能になります。
CASE文
DB上である程度の表示形式に切り替えてデータを取得するケースがあるかと思います。その場合CASE文を使います。
書き方は至って簡単です。そのまま書いて下さい！
//クエリビルダで条件分岐の入ったクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> addSelect('CASE WHEN m.gender =1 THEN "male" WHEN m.gender =2 Then "female" END AS gender_name')
-> from('master', "m");
SELECT m.id,m.name,m.date, CASE WHEN m.gender =1 THEN "male" WHEN m.gender =2 Then "female" END AS gender_name FROM master m
WHERE句
条件を指定し、取得したい行を絞り込むために利用します。
//クエリビルダでWHERE句があるクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> where('m.id = 1');
SELECT m.id,m.name,m.date FROM master m WHERE m.id = 1
idが1番のレコードのid,name,dateの列を取得することができます。
ただこの方式はあまり使わないです。idを引数にして動的に情報を取るケースがほとんどかと。
なのでプレースホルダで指定する方法をよく使います。
//idに1を指定する
$id = 1;
//クエリビルダでクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> where('m.id = :id')
-> setParameter(':id', $id);
SELECT m.id,m.name,m.date FROM master m WHERE m.id = 1
複数のWHERE句 and編
論理積条件指定をしたい場合、andWhereを使います。
//性別が男性で、退会日が12月15日23:59:59以前のデータ
$gender = 1;
$date = "2018-12-15 23:59:59";
//クエリビルダでクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> where('m.gender = :gender')
-> andWhere('m.delete_date <= :delete_date')
-> setParameter(':gender', $gender )
-> setParameter(':delete_date', $date);
SELECT m.id,m.name,m.date FROM master m WHERE (m.gender = 1) AND (m.delete_date <= "2018-12-15 23:59:59")
複数のWHERE句 or編
論理和条件指定をしたい場合、orWhereを使います。
//タイプが1か5のレコードを取得する
$type1 = 1;
$type2 = 5;
//クエリビルダでクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,m.type')
-> from('master', "m")
-> where('m.type = :type1')
-> orWhere('m.type = :type2')
-> setParameter(':type1', $type1)
-> setParameter(':type2', $type2);
SELECT m.id,m.name,m.date,m.type FROM master m WHERE (m.type = 1) OR (m.type = 5)
複数のWHERE句 and or混在編
ANDとORの複合条件を指定したいケースもあると思います。
例えば、性別が男で、タイプが1か3の場合など。
//クエリビルダで性別が男で、タイプが1か3のクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,m.type')
-> from('master', "m")
-> where('m.gender = 1')
-> andWhere('m.type = 1 or m.type = 3');
その場合は、andWhereでorを直接記載することで対応可能です。
SELECT m.id,m.name,m.date,m.type FROM master m WHERE (m.gender = 1) AND (m.type = 1 or m.type = 3)
LIKE検索
検索で言うところの部分一致や前方一致を使う場合、%で値を囲みますが、この場合はsetParameter部分で囲います。
//名前にOを含む行を取得
$name="o";
//クエリビルダで部分一致のクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,m.type')
-> from('master', "m")
-> where('m.name LIKE :name')
-> setParameter(':name', "%".$name."%");
SELECT m.id,m.name,m.date,m.type FROM master m WHERE m.name LIKE "%o%"
同様に一致条件を変える場合
- 前方一致
-> setParameter(':type1', $type1."%");
- 後方一致
-> setParameter(':type1', "%".$type1);
と％を追記する箇所を変えることで対応可能です。
IN句
idが1,3,5,7,9のレコードを取得する等、複数の条件を指定する時に使います。
//IN句として指定したい内容を配列で指定
$ids = array(1,3,5,7,9);
//クエリビルダでIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> where('m.id IN (:id)')
-> setParameter(':id', $ids, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
SELECT m.id,m.name,m.date FROM master m WHERE m.id IN (1,3,5,7,9)
IN句の様にパラメータに配列を指定する時は、setParameter の第三引数に
引数が数値の配列のときは
\Doctrine\DBAL\Connection::PARAM_INT_ARRAY
引数が文字列の配列のときは
\Doctrine\DBAL\Connection::PARAM_STR_ARRAY
をつけて下さい。 これを忘れると動かなくなりますので注意が必要です。
JOIN句
JOIN句には4つの引数が必要です。
第一引数：JOINされるテーブルのエイリアス
第二引数：JOINしたいテーブルのテーブル名
第三引数：JOINしたいテーブルのエイリアス
第四引数：JOINする条件
join('m', 'segment', 's', 'm.id = s.id');
上記の場合、
第一引数：m (masterテーブルのエイリアス)
第二引数：segment
第三引数：s (segmentテーブルのエイリアス)
第四引数：masterのid と segmentのid で結合
という意味となります。
INNER JOIN
//クエリビルダでInnerJOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
-> from('master', "m")
-> join('m', 'segment', 's', 'm.id = s.id');
または
//クエリビルダでINNER JOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
-> from('master', "m")
-> innerJoin('m', 'segment', 's', 'm.id = s.id');
SELECT m.id,m.name,m.date,s.age FROM master m INNER JOIN segment s ON m.id = s.id
LEFT JOIN
//クエリビルダでLEFT JOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
-> from('master', "m")
-> leftJoin('m', 'segment', 's', 'm.id = s.id');
SELECT m.id,m.name,m.date,s.age FROM master m RIGHT JOIN segment s ON m.id = s.id
RIGHT JOIN
//クエリビルダでRIGHT JOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
-> from('master', "m")
-> rightJoin('m', 'segment', 's', 'm.id = s.id');
SELECT m.id,m.name,m.date,s.age FROM master m LEFT JOIN segment s ON m.id = s.id
サブクエリ
サブクエリを利用する場合、クエリビルダ自体を2つインスタンス化しなくてはなりません。
//クエリビルダをインスタンス化
$queryBuilder = new QueryBuilder($db_connection);
//サブクエリ用のクエリビルダをインスタンス化
$subQueryBuilder = new QueryBuilder($db_connection);
//サブクエリのクエリビルダで既婚者のIDを取得する
$subQueryBuilder -> select('s.id')
-> from('segment', "s")
-> where('s.marriage = 1');
//クエリビルダでinner joinするクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> innerJoin('m', sprintf('(%s)', $subQueryBuilder->getSQL()),"s","m.id = s.id");
SELECT m.id,m.name,m.date FROM master m INNER JOIN (SELECT s.id FROM segment s WHERE s.marriage = 1) s ON m.id = s.id
サブクエリのSQLを
getSQL()
メソッドで取得し、join する対象テーブルとしています。
GROUP BY
集計条件を指定する場合は、以下の様にgroupBy()を追加します。
//クエリビルダで性別カラムでグループ化するクエリを構築する
$queryBuilder -> select('count(m.id),gender')
-> from('master', "m")
-> groupBy('gender');
SELECT count(m.id),gender FROM master m GROUP BY gender
追加でグループ化条件を追加する場合
//クエリビルダで性別カラム及び登録年月でグループ化するクエリを構築する
$queryBuilder -> select('count(m.id),gender,DATE_FORMAT(m.date,"%Y%m")')
-> from('master', "m")
-> groupBy('gender')
-> addGroupBy('DATE_FORMAT(m.date,"%Y%m")');
SELECT count(m.id),gender,DATE_FORMAT(m.date,"%Y%m") FROM master m GROUP BY gender, DATE_FORMAT(m.date,"%Y%m")
で追加条件で集計可能です。
HAVING句
集計結果の中での条件抽出をする場合にHAVINGはhaving()を使います。
//クエリビルダで性別カラムでグループ化し、性別が1のものの件数を取得するクエリを構築する
$queryBuilder -> select('gender,count(m.id)')
-> from('master', "m")
-> groupBy('gender')
-> having('gender = 1');
追加でHAVING条件を追加する場合
//クエリビルダで性別カラムでグループ化し、性別が1のもので、年月が201812のものの件数を取得するクエリを構築する
$queryBuilder -> select('gender,count(m.id),DATE_FORMAT(m.date,"%Y%m") as Ym')
-> from('master', "m")
-> groupBy('gender')
-> having('gender = 1')
-> andHaving('Ym <= "201812"');
SELECT gender,count(m.id),DATE_FORMAT(m.date,"%Y%m") as Ym FROM master m GROUP BY gender HAVING (gender = 1) AND (Ym <= "201812")
ORでHAVING条件を追加する場合
//クエリビルダでタイプカラムでグループ化し、タイプが1か5のものの件数を取得するクエリを構築する
$queryBuilder -> select('type,count(m.id)')
-> from('master', "m")
-> groupBy('gender')
-> having('type = 1')
-> orHaving('type = 5');
SELECT type,count(m.id) FROM master m GROUP BY gender HAVING (type = 1) OR (type = 5)
ORDER句
ソートは以下のように対応します。
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> orderBy("m.id","DESC");
SELECT m.id,m.name,m.date FROM master m ORDER BY m.id DESC
複合ソートは以下のように対応します。
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> orderBy("m.date","DESC")
-> addOrderBy("m.name","ASC");
SELECT m.id,m.name,m.date FROM master m ORDER BY m.date DESC, m.name ASC
LIMIT句
最初から5件のレコードを取得
//クエリビルダでLIMIT句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> setMaxResults(5);
SELECT m.id,m.name,m.date FROM master m LIMIT 5
6件目から3件のレコードを取得
//クエリビルダでLIMIT 6,3に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
-> from('master', "m")
-> setMaxResults(3)
-> setFirstResult(5);
SELECT m.id,m.name,m.date FROM master m LIMIT 3 OFFSET 5
まとめ
サイト構築においてQueryBuilderを使うと開発効率は素のSQLを書くよりも残念ながら下がってしまいます。
ですが、論理的、合理的にSQLを作成することで後々のメンテコストは下がり
攻撃を受ける危険性のある文字列結合によるSQL生成を回避できます。
是非使ってみて下さいね！