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

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

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

## 準備するもの

- 適当なPHP MVCフレームワーク・・・・・・・・・1式
- doctrine/dbal ・・・・・・・・・・・・・・・・・1式 (執筆時 version 2.8.0利用）
- 接続用DBとテーブル(今回はMYSQLを使います) ・・1式
    - DBのSQLは以下参照


```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とは

https://www.doctrine-project.org/api/dbal/2.8/Doctrine/DBAL/Query/QueryBuilder.html

読んで字の如し。クエリを作る機能です。データベースに問い合わせに行く際に指定が必要な項目を
パーツ毎に分け、論理的且つ合理的にクエリを作ることができるので、大変便利です。
DB検索で検索条件が複数ある場合、条件分岐等でクエリを構築することが容易にできます。
必要最低限な準備として、DBALConnectionで生成したDB接続のインスタンスが必要となります。
※以降便宜上、DBコネクション（`$db_connection`）として表記します。
DBのコネクションが準備できたら、QueryBuilderオブジェクトを、DBコネクションを引数としてインスタンス化します。

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

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

```php:getSQL()
echo $queryBuilder->getSQL();
```
を追記することで生成されたクエリを確認することができます。

### 適当なクエリは作れない

QueryBuilderをインスタンス化する際に、引数にDBコネクションを設定しているのには理由があります。
それは、**本当に存在しているテーブルや、カラム名を使わないと、エラーが出る**ところ。
単に、仮想のクエリ文字列を作るだけではなく、存在確認などをきちんと行っているので、Typoしても安心です。

### エスケープ処理をちゃんとしてくれる

一度は聞いたことがあると思いますが、「SQLインジェクション攻撃」に対しても有効です。

```php
//悪意によって全件取得用のクエリを投入されるケース
$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

### 全列全件取得

```php
//クエリビルダで全列全件取得クエリを構築する
$queryBuilder -> select('*')
              -> from('master', "m");
```
```sql:実行結果
SELECT * FROM master m
```
`master`テーブルにある全ての列、行を取得することができます。

テーブル名にはエイリアスが設定可能です。
後述のJoinで結合した際、他テーブルに同一カラム名がある場合などを想定し、指定しておくことをお勧めします。

### 指定列全件取得

```php
//クエリビルダで指定列全件取得クエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m");
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m
```

`master`テーブルにある`id`,`name` ,`date`の列、すべての行を取得することができます。

### 指定列を後から追加

```php
//クエリビルダで指定列全件取得クエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m");

//削除フラグがOnの場合は削除日も合わせて取得する
if($delete_flag === true){
    $queryBuilder -> addSelect('m.delete_date');
}
```
```sql:実行結果
SELECT m.id,m.name,m.date, m.delete_date FROM master m
```


特定条件のときは、`delete_date`列を追加で取得する時等、対応が可能になります。

### CASE文

DB上である程度の表示形式に切り替えてデータを取得するケースがあるかと思います。その場合CASE文を使います。
書き方は至って簡単です。そのまま書いて下さい！

```php
//クエリビルダで条件分岐の入ったクエリを構築する
$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");
```
```sql:実行結果
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句

条件を指定し、取得したい行を絞り込むために利用します。

```php
//クエリビルダでWHERE句があるクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m")
              -> where('m.id = 1');
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m WHERE m.id = 1
```
`id`が1番のレコードの`id`,`name`,`date`の列を取得することができます。
ただこの方式はあまり使わないです。idを引数にして動的に情報を取るケースがほとんどかと。
なのでプレースホルダで指定する方法をよく使います。

```php
//idに1を指定する
$id = 1;

//クエリビルダでクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m")
              -> where('m.id = :id')
              -> setParameter(':id', $id);
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m WHERE m.id = 1
```
### 複数のWHERE句 and編

論理積条件指定をしたい場合、andWhereを使います。

```php
//性別が男性で、退会日が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);
```
```sql:実行結果
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を使います。

```php
//タイプが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);
```
```sql:実行結果
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の場合など。

```php

//クエリビルダで性別が男で、タイプが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を直接記載することで対応可能です。

```sql:実行結果
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部分で囲います。

```php

//名前にOを含む行を取得
$name="o";

//クエリビルダで部分一致のクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,m.type')
              -> from('master', "m")
              -> where('m.name LIKE  :name')
              -> setParameter(':name', "%".$name."%");
```

```sql:実行結果
SELECT m.id,m.name,m.date,m.type FROM master m WHERE m.name LIKE "%o%"
```

同様に一致条件を変える場合

- 前方一致

```php
              -> setParameter(':type1', $type1."%");
```
- 後方一致

```php
              -> setParameter(':type1', "%".$type1);
```
と％を追記する箇所を変えることで対応可能です。



## IN句

idが1,3,5,7,9のレコードを取得する等、複数の条件を指定する時に使います。

```php

//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);
```
```sql:実行結果
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する条件

```php
join('m', 'segment', 's', 'm.id = s.id');
```
上記の場合、

第一引数：**m** (masterテーブルのエイリアス)
第二引数：**segment**
第三引数：**s** (segmentテーブルのエイリアス)
第四引数：**master**の**id** と **segment**の**id** で結合

という意味となります。

### INNER JOIN

```php
//クエリビルダでInnerJOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
            -> from('master', "m")
            -> join('m', 'segment', 's', 'm.id = s.id');
```
または

```php
//クエリビルダでINNER JOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
              -> from('master', "m")
              -> innerJoin('m', 'segment', 's', 'm.id = s.id');
```
```sql:実行結果
SELECT m.id,m.name,m.date,s.age FROM master m INNER JOIN segment s ON m.id = s.id
```
### LEFT JOIN

```php
//クエリビルダでLEFT JOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
              -> from('master', "m")
              -> leftJoin('m', 'segment', 's', 'm.id = s.id');
```
```sql:実行結果
SELECT m.id,m.name,m.date,s.age FROM master m RIGHT JOIN segment s ON m.id = s.id
```

### RIGHT JOIN

```php
//クエリビルダでRIGHT JOIN句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date,s.age')
              -> from('master', "m")
              -> rightJoin('m', 'segment', 's', 'm.id = s.id');
```
```sql:実行結果
SELECT m.id,m.name,m.date,s.age FROM master m LEFT JOIN segment s ON m.id = s.id
```

### サブクエリ

サブクエリを利用する場合、クエリビルダ自体を2つインスタンス化しなくてはなりません。

```php

//クエリビルダをインスタンス化
$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()を追加します。

```php
//クエリビルダで性別カラムでグループ化するクエリを構築する
$queryBuilder -> select('count(m.id),gender')
              -> from('master', "m")
              -> groupBy('gender');
```
```sql:実行結果
SELECT count(m.id),gender FROM master m GROUP BY gender
```

追加でグループ化条件を追加する場合

```php
//クエリビルダで性別カラム及び登録年月でグループ化するクエリを構築する
$queryBuilder -> select('count(m.id),gender,DATE_FORMAT(m.date,"%Y%m")')
            -> from('master', "m")
            -> groupBy('gender')
            -> addGroupBy('DATE_FORMAT(m.date,"%Y%m")');
```
```sql:実行結果
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()を使います。

```php
//クエリビルダで性別カラムでグループ化し、性別が1のものの件数を取得するクエリを構築する
$queryBuilder -> select('gender,count(m.id)')
              -> from('master', "m")
              -> groupBy('gender')
              -> having('gender = 1');
```
追加でHAVING条件を追加する場合

```php
//クエリビルダで性別カラムでグループ化し、性別が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"');
```
```sql:実行結果
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条件を追加する場合

```php
//クエリビルダでタイプカラムでグループ化し、タイプが1か5のものの件数を取得するクエリを構築する
$queryBuilder -> select('type,count(m.id)')
              -> from('master', "m")
              -> groupBy('gender')
              -> having('type = 1')
              -> orHaving('type = 5');
```
```sql:実行結果
SELECT type,count(m.id) FROM master m GROUP BY gender HAVING (type = 1) OR (type = 5)
```
## ORDER句
ソートは以下のように対応します。

```php
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m")
              -> orderBy("m.id","DESC");
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m ORDER BY m.id DESC
```

複合ソートは以下のように対応します。

```php
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m")
              -> orderBy("m.date","DESC")
              -> addOrderBy("m.name","ASC");
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m ORDER BY m.date DESC, m.name ASC
```
## LIMIT句

### 最初から5件のレコードを取得

```php

//クエリビルダでLIMIT句に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m")
              -> setMaxResults(5); 
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m LIMIT 5
```
### 6件目から3件のレコードを取得

```php
//クエリビルダでLIMIT 6,3に相当するクエリを構築する
$queryBuilder -> select('m.id,m.name,m.date')
              -> from('master', "m")
              -> setMaxResults(3)
              -> setFirstResult(5); 
```
```sql:実行結果
SELECT m.id,m.name,m.date FROM master m LIMIT 3 OFFSET 5
```
## まとめ

サイト構築においてQueryBuilderを使うと開発効率は素のSQLを書くよりも残念ながら下がってしまいます。
ですが、論理的、合理的にSQLを作成することで後々のメンテコストは下がり
攻撃を受ける危険性のある文字列結合によるSQL生成を回避できます。

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