How can i re-write the query in cakephp-3 way?

Question

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.

i suggest to use Countercache behavior to keep track of comment count see more book.cakephp.org/3.0/en/orm/behaviors/counter-cache.html — N Nem
you can alias subquery with innerjoin like this $this->Posts->find()->innerJoin(['d' => $query], $conditions); see more api.cakephp.org/3.2/… — N Nem
Please format your sql to make it more readable when you post your questions. There is a ton of free sql-formatters online. — JazzCat

José Lorenzo Rodríguez José Lorenzo Rodríguez · Accepted Answer · 2016-02-11T09:18:55

The alias for a join is made this way:

$query->innerJoin(['the_alias' => $subquery], $onConditions);

In your case:

$comments = TableRegistry::get('Comments');
$subquery = $comments->find();
$subquery->select(['post_id' => 'post_id','cnt' => $query->func()->count('post_id')])
    ->where(['mark'=>1])
    ->group(['post_id']);

$postsTable->find()
    ->innerJoin(['d' => $subquery], ['Posts.id = d.post_id'])
    ->order(['d.cnt' => 'DESC']);

How can i re-write the query in cakephp-3 way?

1 Answers