【PHP】CakePHP3.6のクエリービルダーで２つのテーブルを集計する方法

やりたかったこと

2つのテーブルを集計する。

・「ATables」テーブルでグループ毎の「key」項目の数を求める。　→　集計結果をnumberとする。

・「ATables」テーブルの集計結果と、「BTables」テーブルの「number」項目の合計を求める

SQL上では実現できていたのですが、CakePHPでどのようにすれば良いのか

調べても見つからず、かなりハマりました。

以下のようにして実現できました。

// モデル読込み
$aTables = TableRegistry::get('ATables');
$bTables = TableRegistry::get('BTables');

// Aテーブルのクエリ作成
$aTablesQuery = $aTables->find();
$aTablesQuery->select(['id', 'key', 'number' => $aTablesQuery->func()->count('key')])->where(['test_flag <=' => '1'])->group(['id', 'key']);

// Bテーブルのクエリ作成
$bTablesQuery = $bTables->find()->select(['id', 'key', 'number']);

// テーブル結合
$unionQuery = $bTablesQuery->unionAll($aTablesQuery);

// 最終クエリ作成
$query = $this
    ->find()
    ->select(['id' => 'BTables__id', 'key' => 'BTables__keyword', 'number' => $this->find()->func()->sum('BTables__number')])
    ->from([$this->alias() => $unionQuery])
    ->group(['id, key']);

// クエリ実行
$query->all();

 UNIONした結果は「BTables__id」のようにテーブル名が先頭に付くみたいなので、気を付けないといけません。

「from()」にUNIONしたクエリを渡さないといけません。ここはハマりました。