cakephp3【alpha1】クエリービルダー２ - m_shige1979のときどきITブログ

クエリービルダーでちょびっとネスト

(id = :c0 AND id = :c1 AND (aaa IN (:c2,:c3) OR bbb >= :c4) AND xxx != :c5 AND NOT ((vvv = :c6 AND yyy = :c7)) AND hhh LIKE :c8)

=やIN、OR、ANDなどをごちゃごちゃに組み込むとこんな感じ

↓

サンプル

<?php
namespace App\Console\Command;

use Cake\Console\Shell;
use Cake\ORM\TableRegistry;

/**
 * Sample4 shell command.
 */
class Sample4Shell extends Shell {

/**
 * main() method.
 *
 * @return bool|int Success or error code.
 */
	public function main() {

        $members = TableRegistry::get('Members');
        $query = $members->query();
        var_dump(
            $query->select()->where(function($exp) {

                $conditions = $exp->and_(function($_and){
                    return $_and
                        ->eq('id', 10)
                        ->eq('id', 20)
                        ->add($_and->or_(function($_or){
                            return $_or
                                ->in('aaa', [10, 20])
                                ->gte('bbb', 20)
                            ;
                        }))
                        ->notEq('xxx', 20)
                        ->not($_and->and_(function($_and){
                            return $_and
                                ->eq('vvv', 10)
                                ->eq('yyy', 30)
                            ;
                        }))
                        ->like('hhh', '%sss%')
                        ;
                });

                return $conditions;

            })->sql()
        );

	}

}

↓

SELECT 
    Members.id AS `Members__id`, 
    Members.name AS `Members__name`, 
    Members.age AS `Members__age`, 
    Members.tel AS `Members__tel`, 
    Members.created AS `Members__created`, 
    Members.modified AS `Members__modified` 

FROM 
    members AS Members 
WHERE 
    (
        id = :c0 
        AND id = :c1 
        AND (
            aaa IN (:c2,:c3) 
            OR bbb >= :c4
        ) 
        AND xxx != :c5 
        AND NOT (
            (vvv = :c6 AND yyy = :c7)
        ) 
        AND hhh LIKE :c8)

メソッド

and_、or_

これらは内部に条件を指定して、内部で指定した条件をandまたはorで連結するもの

eq、in、likeなど

and_、or_の中で指定する条件で項目との値のチェックに使用

and_や　or_を括るときに使用し、その際、その中野条件をNOTで挟む

確認

以下のデータ

mysql> select * from members;
+----+--------------------------+------+--------+---------+--------------+---------------------+---------------------+
| id | name                     | age  | status | deleted | tel          | created             | modified            |
+----+--------------------------+------+--------+---------+--------------+---------------------+---------------------+
|  1 | テスとユーザー１         |   20 |      0 |       0 | 000-111-2222 | 2014-06-07 21:37:25 | 2014-06-07 21:37:25 |
|  2 | テスとユーザー２         |   18 |      0 |       0 | 0001112222   | 2014-06-07 21:38:24 | 2014-06-07 21:38:24 |
|  3 | テスとユーザー３         |   18 |      0 |       0 | 0001113333   | 2014-06-07 21:41:48 | 2014-06-07 21:41:48 |
+----+--------------------------+------+--------+---------+--------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

php

<?php
namespace App\Console\Command;

use Cake\Console\Shell;
use Cake\ORM\TableRegistry;

/**
 * Sample4 shell command.
 */
class Sample4Shell extends Shell {

/**
 * main() method.
 *
 * @return bool|int Success or error code.
 */
	public function main() {

        $members = TableRegistry::get('Members');
        $query = $members->find();

        $list =  $query->select()->where(function($exp) {

                $conditions = $exp->and_(function($_and){
                    return $_and
                        ->eq('deleted', 0)
                        ->add($_and->or_(function($_or){
                            return $_or
                                ->in('age', [20, 18])
                                ->gte('age', 20)
                            ;
                        }))
                        ->notEq('deleted', 1)
                        ->not($_and->and_(function($_and){
                            return $_and
                                ->eq('status', 0)
                                ->eq('tel', '000-111-2222')
                            ;
                        }))
                        ->like('name', 'テス%')
                        ;
                });

                return $conditions;

            }
        );

        var_dump($list->sql());

        foreach($list as $item){
            var_dump(
                $item->id,
                $item->name,
                $item->age,
                $item->tel,
                $item->status,
                $item->created->__toString(),
                $item->modified->__toString()
            );
            echo "----\n";
        }

	}

}

結果

[vagrant@localhost alpha1]$ sh app/src/Console/cake sample4

Welcome to CakePHP v3.0.0-alpha1 Console
---------------------------------------------------------------
App : src
Path: /vagrant/projects/alpha1/app/src/
---------------------------------------------------------------
string(444) "SELECT Members.id AS `Members__id`, Members.name AS `Members__name`, Members.age AS `Members__age`, Members.status AS `Members__status`, Members.deleted AS `Members__deleted`, Members.tel AS `Members__tel`, Members.created AS `Members__created`, Members.modified AS `Members__modified` FROM members AS Members WHERE (deleted = :c0 AND (age IN (:c1,:c2) OR age >= :c3) AND deleted != :c4 AND NOT ((status = :c5 AND tel = :c6)) AND name LIKE :c7)"
int(2)
string(24) "テスとユーザー２"
int(18)
string(10) "0001112222"
int(0)
string(14) "6/7/14 9:38 PM"
string(14) "6/7/14 9:38 PM"
----
int(3)
string(24) "テスとユーザー３"
int(18)
string(10) "0001113333"
int(0)
string(14) "6/7/14 9:41 PM"
string(14) "6/7/14 9:41 PM"
----
[vagrant@localhost alpha1]$

まあ、それっぽく出ている感じ。

複数のテーブルを使用する際にどうするか？

SEELECT
    *
FROM
    users left outer join (
        SELECT
            id
        FROM
            members
        WHERE
            group = 10
    ) AS T1
    ON T1.id = users.id

こんなこととかいろいろ調べないといけない