Cakephp3 QueryBuilderについて

はじめに

QueryBuilderを利用すると、SQLを直接利用することなくデータベースにアクセスを行うことができます。
SQL的な記述を極力なくし、PHPのメソッドのメソッド呼び出しを組み合わせること(メソッドチェーン)で、データベースアクセスを行う仕組みとなっています。

それに対して、ConnectionManagerを使えば、直接SQLを発行することができます。

前提

Boardsテーブルに、name,title,contentのカラムが用意されているとします

QueryBuliderとは？

データベースの利用はいかにして検索を組み立てていくことが重要です。
findメソッドを使用することでさまざまな検索を行うことができるが、findの引数に細かく設定情報していくのは可読性があまり良いとはいえないのかなと思います。

QueryBuilderのメソッドは同じくfindですが、このfindでは【Query】というクラスのインスタンスが返されます。
この【Query】というクラスのインスタンスがQueryBuilderのメインになります。

メソッドを連続して呼び出していく書き方で記述します。
この記述方法を一般に【メソッドチェーン】といいます。

例：

$data = $this->Boards
              ->find()
              ->where(['id <='=>$input])
　　　　　　　　->order(['id'=>'DESC']);
            }

nameが等しいレコードを抽出する

src/Template/Boards/index.ctp

<h1>Database サンプル</h1>
<?=$this->Form->create($entity)?>
<fieldset>
    <?=$this->Form->text("input")?>
</fieldset>
<?=$this->Form->button("送信")?>
<?=$this->Form->end() ?>

<table>
    <?php foreach($data as $obj): ?>
    <tr>
        <td><?=$obj ?></td>
    </tr>
    <?php endforeach; ?>
</table>

/src/Controller/BoardsController.php

<?php 
    namespace App\Controller;
    class BoardsController extends AppController {
        public function index($id = null){
            $data = $this->Boards->find();
            if($this->request->is('post')) {
                $input = $this->request->data['input'];
                $data = $this->Boards->find()->where(['name'=>$input]);
            }
            $this->set('data',$data);
            $this->set('entity',$this->Boards->newEntity());
        }

フォームに入力されたID番号よりも小さいレコードを降順で出力する

（不等号でクエリを投げるパターン）

public function index($id = null){
            $data = $this->Boards->find();
            if($this->request->is('post')) {
                $input = $this->request->data['input'];
                $data = $this->Boards
                    ->find()
                    ->where(['id <='=>$input])
　　　　　　　　　　　->order(['id'=>'DESC']);
            }
            $this->set('data',$data);
            $this->set('entity',$this->Boards->newEntity());
        }

その他のクエリーのパターンとして

->offset //指定の位置から取得する
->limit //指定の数だけ取得する

//例
->find()
->offset(5)
->limit(3);

複数のwhere条件

->andWhere(条件);
->orWhere(条件);

selectによる項目の設定

->select(配列);

あいまい検索でのor条件

/src/Controller/BoardsController.php

<?php 
    namespace App\Controller;
    class BoardsController extends AppController {
        public function index($id = null){
            $data = $this->Boards->find();
            if($this->request->is('post')) {
                $input = $this->request->data['input'];
                $data = $this->Boards
                    ->find()
                    ->where(['name like'=>'%'.$input.'%'])
                    ->orWhere(['title like'=>'%'.$input.'%'])
                    ->orWhere(['content like'=>'%'.$input.'%'])
　　　　　　　       ->select(['name','title']);
            }
            $this->set('data',$data);
            $this->set('entity',$this->Boards->newEntity());
        }

whereに関数を渡すパターン

下記のように、不等号の演算記号まで含めた値をキーとして用意することもできるが

->where(['id <='=>$input])

whereの引数に関数を指定し、細かい条件設定をするパターンもあります。

->where(
  function($exp,$q) {
     return(Expression);
  }
)

下記は、nameにinputとなっている値とIDが一致していたら、そのレコードを表示する記述です。

<?php 
    namespace App\Controller;
    class BoardsController extends AppController {
        public function index($id = null){
            $data = $this->Boards->find();
            if($this->request->is('post')) {
                $input = $this->request->data['input'];
                $data = $this->Boards
                    ->find()
                    ->where(function($exp,$q) use($input) {
                        return $exp->eq('id',$input);
                    });
            }
            $this->set('data',$data);
            $this->set('entity',$this->Boards->newEntity());
        }

$exp　は Expressionクラスのインスタンスです。このExpressionに用意されている条件式に関するメソッドを使用することで
さまざまな条件を作ることができます

eq(項目名,値);// A = B
notEq(項目名,値);// A != B
lt(項目名,値);// A < B
lte(項目名,値);// A <= B
gt(項目名,値);// A > B
gte(項目名,値);// A >= B

ちなみに
lt →　less than
gt →　greater than
lte →　less than equal
gte →　greater than equal 　の略