CakePHP3でクエリビルダーを使用してサブクエリの書き方を教えてください！

Question

CalePHP3の勉強を始めました。
現在一箇所困っている箇所があります。

Membersテーブル
Contentsテーブル
ContentsReviewsテーブル
のような構成で、開発を行っているのですが、下記SQLをどうようにしてクエリビルダーで実現すればよろしいでしょうか？

SELECT
*
FROM
Members
INNER JOIN
Contents
ON Members.id = Contents.member_id
Left JOIN
(
SELECT AVG(SCORE) AS SCORE , Content_id
FROM ContentsReviews
GROUP BY Content_id
) AS Review
ON Content.id = Review.Content_id
Order BY SCORE DESC

どうぞよろしくお願いいたします。

Answer 1

こんな感じでいかがでしょうか？
※最後に取得するカラムは、適当に指定してください

		$this->loadModel("Contentsreviews");　// これはMembersControllerで処理することを想定しています

    	$subquery = $this->Contentsreviews->find();
    	$subquery->select(['SCORE' => $subquery->func()->avg('score'),
    						'content_id' => 'content_id'
    					])
    			->group('content_id');

    	$query = $this->Members->find()
    			->select(['members.id','members.name','contents.id','contents.name','Review.score']) // ここでのカラム指定はご自身の環境に合わせてください
    			->join([
    					'table' => 'contents',
    					'type' => 'INNER',
    					'conditions' => 'contents.member_id = Members.id'
    			])
    			->join([
    					'Review' => [
    						'table' => $subquery,
    						'type' => 'LEFT',
    						'conditions' => 'contents.id = Review.Content_id'
    					]
    			])
    			->order(['Review.SCORE' => 'DESC']);

Comments

[ShotaTada]

親切にご回答いただきありがとうございます。
この３日間作業が全く進まなかったのですが、おかげさまで解決できました！
本当にありがとうございます！