CakePHP 3のQueryBuilderの使い方をまとめてみた！

# CakePHP 3のQueryBuilderの使い方をまとめてみた！

こんにちはPHP Matsuriでは色々とお世話になってる[@kozo](http://qiita.com/kozo)です。
この記事は2014年CakePHP Advent Calendarの11日目です

CakePHP 3系からモデルは大きく変わり、**戻り値が配列からオブジェクトになるとか**、**Modelが1ファイルだったものがTableとEntityに分かれる**など色々変わって非常に便利になってます！  
今回は大きく変わったもう1個の**find(SELECT句)のSQLの組み立て方法**について調べてみました。

## find

QueryBuilderの開始地点になり、`SELECT用のQueryオブジェクト`が生成されます。  
`Queryオブジェクト`をメソッドチェーンでつなげることでSQLを組み立てます。  

```
// 基本的な使い方
$this->Users->find()
    ->where(条件A)
    ->where(条件B)
    ->order(ソート条件A)
    ->order(ソート条件B)
    ->select(取得フィールド)
    ->all()
```

## where

`WHERE`句を生成します。  
`where`以外にも`andWhere`, `orWhere`メソッドも存在します。

### 基本的な使い方
```
$this->Users->find()
     ->where(['Users.sex' => 1, 'Users.name LIKE' => '%太郎%']);

// SQL
WHERE "Users"."sex" = 1 AND "Users"."name" LIKE '%太郎%'
```

### メソッドチェーン(上記配列と同じ結果を返す)
```
$this->Users->find()
     ->where(['Users.sex' => 1])
     ->where(['Users.name LIKE' => '%太郎%']);

// SQL
WHERE "Users"."sex" = 1 AND "Users"."name" LIKE '%太郎%'
```

### OR句
```
$this->Users->find()
     ->where(['Users.sex' => 1, 
             'OR' => [['Users.name LIKE' => '%太郎%'], ['Users.name LIKE' => '%花子%']]]);

// SQL
WHERE ("Users"."sex" = 1 AND ("Users"."name" like '%太郎%' OR "Users"."name" like '%花子%')
```

## order

order句を生成します

```
$this->Users->find()
    ->where(['Users.name' => '太郎'])
    ->order(['Users.name' => 'ASC'])
    ->order(['Users.id' => 'DESC']);

// SQL
WHERE "Users"."name" = '太郎' ORDER BY "Users"."name" ASC, "Users"."id" DESC
```

## group, having

`GROUP BY`, `HAVING`を作成します。

```
$this->Users->find()
    ->group(['Users.account_id'])
    ->having(['Users.account_id >' => 100])

// SQL
GROUP BY "Users"."account_id"  HAVING "Users"."account_id" > 100
```

## limit, offset

`LIMIT`, `OFFSET`句を生成します。

```
$this->Users->find()
    ->limit(100)
    ->offset(10)

// SQL
LIMIT 100 OFFSET 10
```

## all, first, toArray

`where`, `order`句等をチェーンでつなげても`SQL`を組み立てようとしただけで、実際のSQLはまだ発行されていない状態です。  
SQLを実行する為には`all`, `first`, `toArray`のメソッドを実行する必要があります。

### メソッドの違い
|メソッド|詳細|
|:-----------|:------------|
|all|SQLを実行して`Iterator`を実装した`ResutSetクラス`を戻します。<br>Iteratorでループしたい場合はこれを利用します|
|first|SQLを実行して`Entityクラス`を`1件`戻します|
|toArray|SQLを実行して`Entityクラス`の`配列`を戻します|
※明示的にメソッド実行しなくても、SQLを組み立てている`Queryオブジェクト`に対して`foreach`でループするタイミングでもSQLが実行されます。


## contain
relation先のテーブルのデータを一緒に取得します。

```
$this->Users->find()
    ->contain(['Address']);
```

## newExpr
newExprは`QueryExpression`オブジェクトを生成します。  
`newExpr`を使うことで複雑なSQLを組み立てやすくなります。   
※`QueryExpression`クラスには、`or_`, `and_`, `eq`, `notEq`, `gt`, `lt`, `gte`, `lte`, `isNull`, `isNotNull`, `like`, `notLike`, `in`, `notIn`, `between`, `not`と一通りそろっています。

```
$or = $query->newExpr()->or_(['Users.name LIKE' => '%次郎%']);
$and = $query->newExpr()->add([['Users.name LIKE' => '%山田%'], ['Users.name LIKE' => '%太郎%']]);
$or->add($and);
$query = $this->Users->find();
$result = $query
    ->where(['Users.id' => 5])
    ->orWhere($or)
    ->all();

// SQL
WHERE (("Users"."name" like '%次郎%' OR ("Users"."name" like '%山田%' AND "Users"."name" like '%太郎%')) OR "Users"."id" = 5)
```

### クロージャーを使うことでもQueryExpressionを利用して同じSQLを発行することが出来ます。

```
$result = $this->Users->find()
    ->where(['Users.id'=>5])
    ->orWhere(function ($exp, $query) {
        $or = $exp->or_([['Users.name LIKE' => '%次郎%']]);
        $and = $exp->and_([['Users.name LIKE' => '%山田%'], ['Users.name LIKE' => '%太郎%']]);
        return $or->add($and);
    })
    ->all();

// SQL
WHERE (("Users"."name" like '%次郎%' OR ("Users"."name" like '%山田%' AND "Users"."name" like '%太郎%')) OR "Users"."id" = 5)
```

## その他

| メソッド名 |概要|
|:-----------|:------------|
|get|プライマリーの値指定で`Entity`を取得します(※Tableクラスのメソッド)|
|count|COUNTのSQLを発行します|
|hydrate|falseを指定することで、Entityで取得するデータの形式を`配列`で取得することが出来ます|
|union, unionAll|UNION句を生成します|
|query|Queryオブジェクトを生成します(※Tableクラスのメソッド)|
|insert|INSERT句を生成します(※saveメソッドは別途あり)|
|update|INSERT句を生成します(※saveメソッドは別途あり)|
|delete|DELETE句を生成します(※deleteメソッドは別途あり)|