クエリービルダー¶

use Cake\ORM\TableRegistry;

// 3.6 より前は、 TableRegistry::get('Articles') を使用
$query = TableRegistry::getTableLocator()->get('Articles')->find();

foreach ($query as $article) {
    debug($article->title);
}

$query->where(['id' => 1]); // 自分自身のクエリーオブジェクトを返します
$query->order(['title' => 'DESC']); // 自分自身を返し、SQL はまだ実行されません。

$query = $articles
    ->find()
    ->select(['id', 'name'])
    ->where(['id !=' => 1])
    ->order(['created' => 'DESC']);

foreach ($query as $article) {
    debug($article->created);
}

debug($articles->find()->where(['id' => 1]));

// 出力
// ...
// 'sql' => 'SELECT * FROM articles where id = ?'
// ...

$resultsIteratorObject = $articles
    ->find()
    ->where(['id >' => 1])
    ->all();

foreach ($resultsIteratorObject as $article) {
    debug($article->id);
}

$resultsArray = $articles
    ->find()
    ->where(['id >' => 1])
    ->toList();

foreach ($resultsArray as $article) {
    debug($article->id);
}

debug($resultsArray[0]->title);

$article = $articles
    ->find()
    ->where(['id' => 1])
    ->first();

debug($article->title);

// Collection ライブラリーの extract() メソッドを使います
// これもクエリーを実行します
$allTitles = $articles->find()->extract('title');

foreach ($allTitles as $title) {
    echo $title;
}

$list = $articles->find('list');

foreach ($list as $id => $title) {
    echo "$id : $title"
}

// Collection ライブラリーの combine() メソッドを使います
// これは find('list') と等価です
$keyValueList = $articles->find()->combine('id', 'title');

// 上級な例
$results = $articles->find()
    ->where(['id >' => 1])
    ->order(['title' => 'DESC'])
    ->map(function ($row) { // map() は Collection のメソッドで、クエリーを実行します
        $row->trimmedTitle = trim($row->title);
        return $row;
    })
    ->combine('id', 'trimmedTitle') // combine() も Collection のメソッドです
    ->toArray(); // これも Collection のメソッドです

foreach ($results as $id => $trimmedTitle) {
    echo "$id : $trimmedTitle";
}

// articles テーブルから id と title だけが選択される
$articles->find()->select(['id', 'title']);

// 計算された slug フィールドを含む、 articles テーブルのすべてのフィールドを取得
$query = $articlesTable->find();
$query
    ->select(['slug' => $query->func()->concat(['title' => 'identifier', '-', 'id' => 'identifier'])])
    ->select($articlesTable); // articles のすべてのフィールドを選択する

$query = $articlesTable->find();

// published フィールドを除く全てのフィールドを取得
$query->selectAllExcept($articlesTable, ['published']);

// SELECT COUNT(*) count FROM ... になる
$query = $articles->find();
$query->select(['count' => $query->func()->count('*')]);

$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
    'Articles.title' => 'identifier',
    ' - CAT: ',
    'Categories.name' => 'identifier',
    ' - Age: ',
    '(DATEDIFF(NOW(), Articles.created))' => 'literal',
]);
$query->select(['link_title' => $concat]);

SELECT CONCAT(Articles.title, :c0, Categories.name, :c1, (DATEDIFF(NOW(), Articles.created))) FROM articles;

$query = $articles->find();
$year = $query->func()->year([
    'created' => 'identifier'
]);
$time = $query->func()->date_format([
    'created' => 'identifier',
    "'%H:%i'" => 'literal'
]);
$query->select([
    'yearCreated' => $year,
    'timeCreated' => $time
]);

// ストアドプロシージャを使う
$query = $articles->find();
$lev = $query->func()->levenshtein([$search, 'LOWER(title)' => 'literal']);
$query->where(function (QueryExpression $exp) use ($lev) {
    return $exp->between($lev, 0, $tolerance);
});

// 生成される SQL はこうなります
WHERE levenshtein(:c0, lower(street)) BETWEEN :c1 AND :c2

$query = $articles->find();
$query->select([
    'count' => $query->func()->count('view_count'),
    'published_date' => 'DATE(created)'
])
->group('published_date')
->having(['count >' => 3]);

$query = $articles->find();
$publishedCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['published' => 'Y']),
        1,
        'integer'
    );
$unpublishedCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['published' => 'N']),
        1,
        'integer'
    );

$query->select([
    'number_published' => $query->func()->count($publishedCase),
    'number_unpublished' => $query->func()->count($unpublishedCase)
]);

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->addCase(
            [
                $q->newExpr()->lt('population', 100000),
                $q->newExpr()->between('population', 100000, 999000),
                $q->newExpr()->gte('population', 999001),
            ],
            ['SMALL',  'MEDIUM', 'LARGE'], # 条件に合致したときの値
            ['string', 'string', 'string'] # それぞれの値の型
        );
    });
# WHERE CASE
#   WHEN population < 100000 THEN 'SMALL'
#   WHEN population BETWEEN 100000 AND 999000 THEN 'MEDIUM'
#   WHEN population >= 999001 THEN 'LARGE'
#   END

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->addCase(
            [
                $q->newExpr()->eq('population', 0),
            ],
            ['DESERTED', 'INHABITED'], # 条件に合致したときの値
            ['string', 'string'] # それぞれの値の型
        );
    });
# WHERE CASE
#   WHEN population = 0 THEN 'DESERTED' ELSE 'INHABITED' END

$query = $articles->find();
$query->enableHydration(false); // エンティティーの代わりに配列を返す
$result = $query->toList(); // クエリーを実行し、配列を返す

[
    ['id' => 1, 'title' => 'First Article', 'body' => 'Article 1 body' ...],
    ['id' => 2, 'title' => 'Second Article', 'body' => 'Article 2 body' ...],
    ...
]

// フィールド、条件、関連が構築済であると仮定します。
$query->formatResults(function (\Cake\Collection\CollectionInterface $results) {
    return $results->map(function ($row) {
        $row['age'] = $row['birth_date']->diff(new \DateTime)->y;
        return $row;
    });
});

// Articles テーブル内のメソッドで
$query->contain(['Authors' => function ($q) {
    return $q->formatResults(function (\Cake\Collection\CollectionInterface $authors) {
        return $authors->map(function ($author) {
            $author['age'] = $author['birth_date']->diff(new \DateTime)->y;
            return $author;
        });
    });
}]);

// 結果を取得する
$results = $query->all();

// 29 が出力される
echo $results->first()->author->age;

$query = $countries->find();
$query->select([
        'year' => $query->func()->year([$query->identifier('created')])
    ])
    ->where(function ($exp, $query) {
        return $exp->gt('population', 100000);
    });

$query = $articles->find()
    ->where(['id' => $ids], ['id' => 'integer[]']);

// もしくは、自動的に配列へとキャストさせるために IN を含めます。
$query = $articles->find()
    ->where(['id IN' => $ids]);

$query = $articles->find()
    ->where(['post_date' => $dates], ['post_date' => 'date[]']);

$query = $categories->find()
    ->where(['parent_id IS' => $parentId]);

$query = $categories->find()
    ->where(['parent_id IS NOT' => $parentId]);

$query = $articles->find();
$expr = $query->newExpr()->add('1 + 1');
$query->select(['two' => $expr]);

$total = $articles->find()->where(['is_active' => true])->count();

$total = $articles->find()->where(['is_active' => true])->limit(10)->count();

$query = $articles->find();
$query->select(['Articles.id', $query->func()->count('Comments.id')])
    ->matching('Comments')
    ->group(['Articles.id']);
$total = $query->count();

$list = $query->all();

$query = $query->where(['is_active' => true])->counter(function ($query) {
    return 100000;
});
$query->count(); // 100000 を返す

$query->cache('recent_articles');

// 文字列で Config 名
$query->cache('recent_articles', 'dbResults');

// CacheEngine のインスタンス
$query->cache('recent_articles', $memcache);

// クエリーの where 句の単純なチェックサムに基づくキーを生成します
$query->cache(function ($q) {
    return 'articles-' . md5(serialize($q->clause('where')));
});

// コントローラーやテーブルのメソッド内で
// 3.5.0 より前は、 contain(['Comments' => function () { ... }]) を使用

$query = $articles->find()->contain('Comments', function (Query $q) {
    return $q
        ->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
});

$this->paginate['contain'] = [
    'Comments' => function (Query $query) {
        return $query->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
    }
];

$query = $articles->find()->contain([
    'Comments',
    'Authors.Profiles' => function (Query $q) {
        return $q->where(['Profiles.is_published' => true]);
    }
]);

// すべての article を取り出すが、承認され (approved)、人気のある (popular) ものだけに限定する
$query = $articles->find()->contain('Comments', function (Query $q) {
    return $q->find('approved')->find('popular');
});

$query = $articles->find()->contain([
    'Authors' => [
        'foreignKey' => false,
        'queryBuilder' => function (Query $q) {
            return $q->where(...); // フィルターのための完全な条件
        }
    ]
]);

// Articles から id と title を、 Users から全列を select する
$query = $articles->find()
    ->select(['id', 'title'])
    ->select($articles->Users)
    ->contain(['Users']);

// Articles から id と title を、 Users、Comments、Tags から全列を select する
$query->select(['id', 'title'])
    ->contain(['Comments', 'Tags'])
    ->enableAutoFields(true) // 3.4.0 より前は autoFields(true) を使用
    ->contain(['Users' => function(Query $q) {
        return $q->autoFields(true);
    }]);

$query->contain([
    'Comments' => [
        'sort' => ['Comments.created' => 'DESC']
    ]
]);

// コントローラーやテーブルのメソッド内で

$query = $articles->find();
$query->matching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

$query = $authors->find();
$query->matching('Articles', function ($q) {
    return $q->where(['Articles.created >=' => new DateTime('-10 days')]);
});

// コントローラーやテーブルのメソッド内で
$query = $products->find()->matching(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

// 渡された変数を使って 'markstory' によってコメントされた記事 (Article) をユニークに取り出す
// ドット区切りのマッチングパスは、ネストされた matching() 呼び出しでも使われます
$username = 'markstory';
$query = $articles->find()->matching('Comments.Users', function ($q) use ($username) {
    return $q->where(['username' => $username]);
});

$query = $articles->find();
$query->innerJoinWith('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

$query = $products->find()->innerJoinWith(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

// コントローラーやテーブルのメソッド内で

$query = $articlesTable
    ->find()
    ->notMatching('Tags', function ($q) {
        return $q->where(['Tags.name' => '退屈']);
    });

$query = $authorsTable
    ->find()
    ->notMatching('Articles', function ($q) {
        return $q->where(['Articles.created >=' => new \DateTime('-10 days')]);
    });

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    });

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    })
    ->matching('Comments');

$query = $articlesTable->find();
$query->select(['total_comments' => $query->func()->count('Comments.id')])
    ->leftJoinWith('Comments')
    ->group(['Articles.id'])
    ->enableAutoFields(true); // 3.4.0 より前は autoFields(true); を使用

$query = $authorsTable
    ->find()
    ->select(['total_articles' => $query->func()->count('Articles.id')])
    ->leftJoinWith('Articles.Tags', function ($q) {
        return $q->where(['Tags.name' => 'awesome']);
    })
    ->group(['Authors.id'])
    ->enableAutoFields(true); // 3.4.0 より前は autoFields(true); を使用

$query = $articles->find()
    ->join([
        'table' => 'comments',
        'alias' => 'c',
        'type' => 'LEFT',
        'conditions' => 'c.article_id = articles.id',
    ]);

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => 'c.article_id = articles.id',
        ],
        'u' => [
            'table' => 'users',
            'type' => 'INNER',
            'conditions' => 'u.id = articles.user_id',
        ]
    ]);

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.created >' => new DateTime('-5 days'),
                'c.moderated' => true,
                'c.article_id = articles.id'
            ]
        ],
    ], ['c.created' => 'datetime', 'c.moderated' => 'boolean']);

// エイリアスと文字列の条件で join する
$query = $articles->find();
$query->leftJoin(
    ['Authors' => 'authors'],
    ['Authors.id = Articles.author_id']);

// エイリアスと配列の条件・型で join する
$query = $articles->find();
$query->innerJoin(
    ['Authors' => 'authors'],
    [
        'Authors.promoted' => true,
        'Authors.created' => new DateTime('-5 days'),
        'Authors.id = Articles.author_id'
    ],
    ['Authors.promoted' => 'boolean', 'Authors.created' => 'datetime']);

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.article_id' => new \Cake\Database\Expression\IdentifierExpression('articles.id')
            ]
        ],
    ]);

$query
    ->where([
        'MATCH (comment) AGAINST (:userData)',
        'created < NOW() - :moreUserData'
    ])
    ->bind(':userData', $userData, 'string')
    ->bind(':moreUserData', $moreUserData, 'datetime');

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->union($inReview);

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->unionAll($inReview);

// 3.6.0 より前は、代わりに association() を使用
$matchingComment = $articles->getAssociation('Comments')->find()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment LIKE' => '%CakePHP%']);

$query = $articles->find()
    ->where(['id IN' => $matchingComment]);

// MySQL の場合、
$query->epilog('FOR UPDATE');