LoginSignup
24
19

More than 5 years have passed since last update.

CeresAdvent Calendar 2018Day 15ceres
@fukumoto

【DBAL queryBuilder】コピペで使えるｶﾓ？チートシート

Last updated at Posted at 2018-12-14

この記事で説明すること/しないこと

  • 説明すること
    • QueryBuilderで何ができるの？
    • SELECT文のみにフォーカスを当てて
  • 説明しないこと
    • インストール部分
    • フレームワークで使えるようにする等の設定

準備するもの

  • 適当なPHP MVCフレームワーク・・・・・・・・・1式
  • doctrine/dbal ・・・・・・・・・・・・・・・・・1式 (執筆時 version 2.8.0利用）
  • 接続用DBとテーブル(今回はMYSQLを使います) ・・1式
    • DBのSQLは以下参照
create_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コネクションを引数としてインスタンス化します。

Connection
use Doctrine\DBAL\Query\QueryBuilder;
$queryBuilder = new QueryBuilder($db_connection);

インスタンス化したQueryBuilderオブジェクトに対し、以降紹介するメソッドで
条件等をメソッドチェーンを使って記載していく形となります。
またメソッド指定が完了した後、どのようなクエリが作られたかを知りたい場合

getSQL()
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");
sql
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生成を回避できます。

是非使ってみて下さいね！

24
19
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
24
19