CakePHP 4 で UNION ALL する方法と Pagination への対応
はじめに
CakePHP 4 にはクエリビルダーの機能があり、生クエリは使わずに、メソッドチェーンでデータ取得を記述することができます。
UNION ALL を行うための関数もあって簡単に使えるのですが、そのままだと Pagination がうまく動きませんでした。
そこで今日は CakePHP 4 で UNION ALL を実装する方法と Pagination に対応する方法をご紹介します。
- CakePHP
- 4.1.2
- MariaDB
- 10.4.11
1. 下準備
今回使用したデータベースと、コントローラの土台です。
CREATE TABLE drinks (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
alcohol tinyint(3) UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO drinks (id, `name`, alcohol) VALUES
(1, 'メロンソーダ', 0),
(2, 'グリーンアップル', 20),
(3, 'アップルジュース', 0);
CREATE TABLE foods (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO foods (id, `name`) VALUES
(1, 'オレンジケーキ'),
(2, 'アップルパイ'),
(3, 'メロンシャーベット');
<?php
declare(strict_types=1);
namespace App\Controller;
class SampleController extends AppController
{
public function index()
{
}
}
上記の drinks と foods テーブルを UNION ALL するサンプルを作ります。
2. UNION ALL の実装方法
CakePHP 4 で UNION ALL は下記のように unionAll() を使って実装できます。
public function index()
{
// Drinks のクエリ
$this->loadModel('Drinks');
$drinkQuery = $this->Drinks->find()
->select([
'name',
'alcohol',
'type' => '"Drink"'
]);
// Foods のクエリ
$this->loadModel('Foods');
$foodQuery = $this->Foods->find()
->select([
'name',
'alcohol' => 'NULL',
'type' => '"Food"'
]);
$items = $drinkQuery->unionAll($foodQuery)->all();
$this->set(compact('items'));
}
<table>
<thead>
<tr>
<th>Type</th>
<th>Name</th>
<th>Alcohol</th>
</tr>
</thead>
<tbody>
<?php foreach ($items as $item) : ?>
<tr>
<td><?= h($item->type) ?></td>
<td><?= h($item->name) ?></td>
<td><?= h($item->alcohol) ?></td>
</tr>
<?php endforeach ?>
</tbody>
</table>
WHERE 句などを使う場合には、モデルごとに指定します
$drinkQuery = $this->Drinks->find()
...
->where(['name LIKE' => '%アップル%']); // ←追加
$foodQuery = $this->Foods->find()
...
->where(['name LIKE' => '%アップル%']); // ←追加
- Unions (Query Builder - CakePHP 4.x Strawberry Cookbook)
- https://book.cakephp.org/4/en/orm/query-builder.html#unions
3. Pagination に対応
Pagination に対応するためには少し工夫が必要のようです。
試行錯誤した結果、下記のように from() で UNION ALL の結果を指定することで実現できました。
public function index()
{
$this->paginate['limit'] = 3;
// Drinks のクエリ
$this->loadModel('Drinks');
$drinkQuery = $this->Drinks->find()
->select([
'name' => 'name',
'alcohol' => 'alcohol',
'type' => '"Drink"'
]);
// Foods のクエリ
$this->loadModel('Foods');
$foodQuery = $this->Foods->find()
->select([
'name' => 'name',
'alcohol' => 'NULL',
'type' => '"Food"'
]);
// UNION ALL した結果から取得
$this->loadModel('Items');
$query = $this->Items->find()
->from(['Items' => $drinkQuery->unionAll($foodQuery)]);
$items = $this->paginate($query);
$this->set(compact('items'));
}
<?php
declare(strict_types=1);
namespace App\Model\Table;
use Cake\ORM\Table;
class ItemsTable extends Table
{
public function initialize(array $config): void
{
parent::initialize($config);
$this->setTable('drinks');
// 扱うフィールド
$this->setSchema([
'name' => 'string',
'alcohol' => 'integer',
'type' => 'string'
]);
}
}
<table>
<thead>
<tr>
<!-- ▼ 変更 -->
<th><?= $this->Paginator->sort('type', 'Type') ?></th>
<th><?= $this->Paginator->sort('name', 'Name') ?></th>
<th><?= $this->Paginator->sort('alcohol', 'Alcohol') ?></th>
</tr>
</thead>
<tbody>
<?php foreach ($items as $item) : ?>
<tr>
<td><?= h($item->type) ?></td>
<td><?= h($item->name) ?></td>
<td><?= h($item->alcohol) ?></td>
</tr>
<?php endforeach ?>
</tbody>
</table>
<!-- ▼ これを追加 -->
<ul>
<li><?= $this->Paginator->counter() ?></li>
<?= $this->Paginator->prev('Prev') ?>
<?= $this->Paginator->next('Next') ?>
</ul>
ItemTable.php を作っているのは Paginator 対策です。
同ファイル内の setTable() で drinks テーブルを指定しているのは、何らかのテーブル指定が必要なためです。
なお WHERE 句については下記のように書くことができます。
$query = $this->Items->find()
...
->where(['name LIKE' => '%アップル%']); // ← 追加
- Paginator (CakePHP 4.x Strawberry Cookbook)
- https://book.cakephp.org/4/en/views/helpers/paginator.html
4. ビューを使う
発想がちょっと変わりますが、データベースのビュー機能でも実現が可能です。
ビューを使えば drinks と foods の UNION ALL の結果を1つのテーブルのように扱うことができます。
そして CakePHP ではビューもテーブルと同様にモデルを作成することができます。
サンプルコードは以下の通りです。
-- この SQL で items ビューを作成
CREATE VIEW items AS
(
SELECT
name,
alcohol,
"Drink" AS type
FROM drinks
)
UNION ALL
(
SELECT
name,
NULL AS alcohol,
"Food" AS type
FROM foods
)
<?php
declare(strict_types=1);
namespace App\Model\Table;
use Cake\ORM\Table;
class ItemsTable extends Table
{
public function initialize(array $config): void
{
parent::initialize($config);
// ビューも setTable() で指定可能
$this->setTable('items');
}
}
public function index()
{
$this->paginate['limit'] = 3;
$this->loadModel('Items');
$query = $this->Items->find();
$items = $this->paginate($query);
$this->set(compact('items'));
}
UNION ALL の部分を書かなくて済むのでコード量は大分減りますね。
下記のように where() も使えます。
$query = $this->Items->find()
->where(['name LIKE' => '%アップル%']); // ← 追加
5. おわりに
今回は UNION ALL で Pagination を使う方法が公式ドキュメントで見当たらなくて、「3. Pagination に対応」で紹介したコードはトライ&エラーを繰り返しました。
試行錯誤の方法としては、まずどのような SQL になるかを考え、そこから CakePHP のクエリビルダーでそれを実現するにはどうしたらいいかを調べていきました。
特に from() と setSchema() の部分は、ドキュメントで情報をみつけられず、CakePHP のソースコードを調べて実装しました。
余談ですがクエリビルダーのデバッグでは「CakePHP 4 でデータ取得時の SQL をコード内で取得、表示する4つの方法」でもご紹介した sql() や sqld() が便利ですので、覚えておくと良いと思います。