Raw sql query:
SELECT Post.id,
Post.title,
Post.mark
FROM posts AS Post
INNER JOIN (SELECT post_id,
Count(post_id) AS cnt
FROM comments
WHERE mark = 1
GROUP BY post_id) AS d
ON Post.id = d.post_id
ORDER BY d.cnt DESC
I'm trying to write this raw sql query in cakephp-3 way.
I have made the inner select query in cakephp-3 way:
$comments = TableRegistry::get('Comments');
$query = $comments->find();
$query->select(['post_id','cnt'=>$query->func()->count('post_id')])
->where(['mark'=>1])
->group(['post_id']);
How can i set alias for this inner query? Then, how can i do inner join with 'Posts' or getting the instance of 'Posts' table how can i do inner join with the inner sql query(the derived comments table)?
Thanks in advance. Any answer will be highly appreciated.