CakePHPではアソシエーションを組むモデルでサブクエリを発行できます。
// At Table Class $subquery = $this->getAssociation('ModelName')->find('all');
例
今、Expenses(支出)とIncomes(収入)というモデルがあり、これらはIncomesは月ごとの収入を持っていて、Expensesは品目と支出額(price)を保持します。 IncomesがhasManyアソシエーションでExpenses(支出)と紐づいているとします。
ここでIncomesテーブルにおいて、月の収入額から支出額の合計を引いた値(月当たりのつまり残金)が多い順に並べたいとき、まず以下のようにincomesのidでExpensesのpriceのSUMを計算します。
// サブクエリ $subquery = $this ->getAssociation('Expenses') ->find('all') ->select([ 'total' => $query->func()->sum('Expenses.price'), 'id' => 'income_id' ]) ->group('Expenses.income_id');
その後、この$subqueryをleftJoinしようとしたのですがここでつまづきました。 subqueryをleftJoinするときに
$query = $this ->find('all') ->leftJoin( ['Expenses' => $subquery], ['Expenses.id' => 'Incomes.id'] ) -> // 以下略
としていると発行したQueryがシングルクォーテーションで囲まれた状態でした。 ちなみに発行されたQueryの内容を確認する方法はこちらの記事に書きました。
ON Expenses.id = 'Incomes.id' // SQL 抜粋
このため意図した通りにLeftJoinされず、Orderもうまく走っていなかったので、
$query = $this ->find('all'); $query = leftJoin( ['Expenses' => $subquery], ['Expenses.id' => $query->newExpr()->add('Incomes.id')] ) -> // 以下略
と変更するとうまくLeftJoinされました。 参考までに。