m_shige1979のささやかな抵抗と欲望の日々

プログラムの勉強をしながら学習したことや経験したことをぼそぼそと書いていきます

なんとなく作ったサイト

http://www.it-check-matome.info/


Github(注意すること)

https://github.com/mshige1979

cakephp3クエリービルダー

SQLを組むほうが基本楽かもしれんが

複数の考え方も必要なので考えてみる。

条件

テーブルを作成してModelのTableも用意しておく
CREATE TABLE members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    tel VARCHAR(15),
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT NULL
);
環境はshell用のプログラムのものより一部抜いています

基本

<?php
    $members = TableRegistry::get('Members');
    $query = $members->query();
    var_dump(
        $query->select()->sql()
    );

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

※基本、selectのみの場合は全部出ます。
sqlメソッドでクエリービルダーで生成したsqlを出力します。

フィールドを指定
<?php
    $members = TableRegistry::get('Members');
    $query = $members->query();
    var_dump(
        $query->select([
            'aaaa' => 'Members.age'
        ], true)->sql()
        ,
        $query->select([
            'count' => 'count(*)'
        ], true)->sql()
    );
結果
SELECT Members.age AS `aaaa` FROM members AS Members
SELECT count(*) AS `count` FROM members AS Members

配列のkeyが別名、valueが実際の値や条件となる

select

引数が未指定の場合はデフォルトで全てを表示する
第1引数:配列、連想配列でフィールドを指定
第2引数:上書きフラグでtrueで第1引数のもののみ出力(デフォルトはfalse)

未指定の場合は対象のデータを生成
        // default
        var_dump(
            $query->select()->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
第2引数をtrueにした場合は第1引数のみ表示
        var_dump(
            $query->select([
                'aaaa' => 'Members.age'
            ], true)->sql()
        );

SELECT 
    Members.age AS `aaaa` 
FROM 
    members AS Members

from

Fromを指定できる
通常は作成したモデルなどを使用するので不要と思われるが独自に別途テーブルが必要な場合など

fromを使用する場合はテーブルのリストを設定する
        var_dump(
            $query->select()->from(['aaa'])->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 
    aaa

※fromを指定した場合は先にselectも指定しておく必要があります。

where

where句より条件を生成する

select句を記載してから生成
        var_dump(
            $query->select()->where(['id' => '1111'])->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 created >= :c1)
複数の条件を指定
        var_dump(
            $query->select()->where(['id' => '1111', 'created >=' => '2014-01-01'])->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 created >= :c1)"
条件をいろいろ付与
        var_dump(
            $query->select()->where([
                'and' => [
                    'id' => '1111',
                    'created >=' => '2014-01-01',
                ],

                'or' => [
                    'name' => ['ccc','ddd'],
                    'id' => 'ddd'
                ]
            ])->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 created >= :c1) AND (name = :c2 OR id = :c3))

※通常はandで連結するけど、orを組み込むことも可能

and

andwhereで別途and条件を付与可能
        var_dump(
            $query->select()->andwhere([
                'id' => '10'
            ])->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

※whereを未指定でも問題ない

andwhereを連続で指定
        var_dump(
            $query->select()->andwhere([
                'id' => '10'
            ])->andWhere([
                'name' => 'aaa'
            ])->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 name = :c1)

or

orwhereも基本はandwhereと同じ
        var_dump(
            $query->select()->orWhere([
                'id' => 'aaa'
            ])->orWhere([
                'id' => 'bbb'
            ])->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 OR id = :c1)

whereでfunction指定?

関数を指定してorやnotなどを指定できる

where句のあとにandwhereなどで関数を呼び出すことも可能
        var_dump(
            $query->select()->where(['name' => 'aaa'])->andWhere(function($exp){
                return $exp->or_([
                    ['id' => '10'], 
                    ['id' => '20']]);
            })->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 
    (name = :c0 AND (id = :c1 OR id = :c2))
inなどを指定したい場合
        var_dump(
            $query->select()->where(['name' => 'aaa'])->andWhere(function($exp){
                return $exp->in(
                    'id', ['10', '20']
                );
            })->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 
    (name = :c0 AND id IN (:c1,:c2))

まとめ

基本的にandで連結、連想配列はキーがかぶったら機能しないので配列でうまくするけど…コールバックメソッドが機能するようになっている。
以前は配列だけで制御する感じがコールバック関数などをうまく使えるようになっているよう
一応、学習コストもそれなりにかかりそうなので少しづつメモとして残していく。

キーの中とかに" >= "とかを設定しているのは嫌な感じがするし。

引き続き調査していく。limitとかjoinとかもあるし
最悪案としては"SQLを直接書いた方"がいいかもしれないけどNoSQLも普及しているしやってみる