CakePHP 4 で UNION ALL する方法と Pagination への対応

はじめに

CakePHP 4 にはクエリビルダーの機能があり、生クエリは使わずに、メソッドチェーンでデータ取得を記述することができます。

UNION ALL を行うための関数もあって簡単に使えるのですが、そのままだと Pagination がうまく動きませんでした。

そこで今日は CakePHP 4 で UNION ALL を実装する方法と Pagination に対応する方法をご紹介します。

CakePHP
4.1.2
MariaDB
10.4.11
目次
  1. 下準備
  2. UNION ALL の実装方法
  3. Pagination に対応
  4. ビューを使う
  5. おわりに

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, 'メロンシャーベット');
/src/Controller/SampleController.php
<?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() を使って実装できます。

/src/Controller/SampleController.php
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'));
}
/templates/Sample/index.php
<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 句などを使う場合には、モデルごとに指定します

/src/Controller/SampleController.php
$drinkQuery = $this->Drinks->find()
    ...
    ->where(['name LIKE' => '%アップル%']); // ←追加

$foodQuery = $this->Foods->find()
    ...
    ->where(['name LIKE' => '%アップル%']); // ←追加

3. Pagination に対応

Pagination に対応するためには少し工夫が必要のようです。
試行錯誤した結果、下記のように from() で UNION ALL の結果を指定することで実現できました。

/src/Controller/SampleController.php
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'));
}
/src/Model/Table/ItemsTable.php
<?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'
        ]);
    }
}
/templates/Sample/index.php
<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 句については下記のように書くことができます。

/src/Controller/SampleController.php
$query = $this->Items->find()
    ...
    ->where(['name LIKE' => '%アップル%']); // ← 追加

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
)
/src/Model/Table/ItemsTable.php
<?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');
    }
}
/src/Controller/SampleController.php
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() も使えます。

/src/Controller/SampleController.php
$query = $this->Items->find()
  ->where(['name LIKE' => '%アップル%']); // ← 追加

5. おわりに

今回は UNION ALL で Pagination を使う方法が公式ドキュメントで見当たらなくて、「3. Pagination に対応」で紹介したコードはトライ&エラーを繰り返しました。

試行錯誤の方法としては、まずどのような SQL になるかを考え、そこから CakePHP のクエリビルダーでそれを実現するにはどうしたらいいかを調べていきました。

特に from()setSchema() の部分は、ドキュメントで情報をみつけられず、CakePHP のソースコードを調べて実装しました。

余談ですがクエリビルダーのデバッグでは「CakePHP 4 でデータ取得時の SQL をコード内で取得、表示する4つの方法」でもご紹介した sql()sqld() が便利ですので、覚えておくと良いと思います。