CakePHP3.x ORMでFrom句に仮想テーブルを追加する方法

結論から言えば

// サブクエリー
$sub_query = $this->Hoges->find();

// ネストしたクエリー
$main_query = $this->Moges->find()->from(['SubTable' => $sub_query]);

これで

SELECT * FROM(SELECT * FROM hoge Hoges) SubTable

といったクエリーがCakePHPから吐き出され、

$main_query->toArray();

などを行う事でEntity型の状態で結果を取得できます。

この機能の肝心なところ

from関数の引数を連想配列にし、テーブルやカラムにエイリアスを設定することです。

from関数に$sub_queryのみを渡した場合

$this->Moges->find()->from($sub_query);

この場合、CakePHPから吐き出されるSQLは

SELECT Moges.* FROM(SELECT * FROM hoge Hoges)

となり、Moges.idがありませんなどのエラーが出力されます。

from関数に連想配列を渡した場合

// サブクエリー
$sub_query = $this->Hoges->find();

// ネストしたクエリー
$main_query = $this->Moges->find()->from(['SubTable' => $sub_query]);

SELECT * FROM(SELECT * FROM hoge Hoges) SubTable

結論で書いたようなSQLが出力されます。

このままではEntityでの取得に苦労する

CakePHPではカラム名のエイリアスとして「テーブルエイリアス__カラム名」といった形で自動的に設定されます。

SELECT id AS 'Hoges__id' FROM hoge Hoges

先ほどのようなコードで取得すると、

// サブクエリー
$sub_query = $this->Hoges->find();

// ネストしたクエリー
$main_query = $this->Moges->find()->from(['SubTable' => $sub_query]);

$result = $main_query->toArray();

foreach($result as $row){
  $id = $row->Hoges__id;
}

このようにデータを引き出す必要があります。
テーブルを連結していた場合は、テーブル毎にエイリアスが変わってしまうので不便ですね。
また、上記コードでは1回のみのネストですが、

SELECT *
FROM(
  SELECT *
  FROM(
    SELECT * FROM hoge Hoges
  )OriginTable
)SubTable

2回以上のネストが存在する場合、SubTableを使ったクエリーを発行する場合に「OriginTable.Hoges__id」で取得できません。

// サブクエリー
$sub_query = $this->Hoges->find()->select(['id' => 'Hoges.id']);

// ネストしたクエリー
$main_query = $this->Moges->find()->from(['SubTable' => $sub_query]);

少し手間がかかりますがselect関数からカラムのエイリアスを設定しましょう。

// エイリアスを設定することでサブクエリーが入っていてもこのようにアクセスが可能になります
$moges = $main_query->toArray();

foreach($moges as $row){
  $id = $row->id;
}

最後に

こんな複雑なクエリーを組む必要はそうそうないと思いますが、誰かのお役に立てれば幸いです。