How can i write this query in cakephp-3 way?

Question

Raw sql query:

SELECT *
FROM
  (SELECT p.id,
          p.title,
          p.mark,

     (SELECT max(created)
      FROM comments c
      WHERE c.post_id=p.id
        AND c.mark=1) AS latest_at
   FROM posts p) AS Post
WHERE Post.latest_at IS NOT NULL
ORDER BY latest_at DESC LIMIT 10

I have these two tables:

$postsTable = TableRegistry::get('Posts');
$comments = TableRegistry::get('Comments');

I have made the inner query in cakephp-3 way:

SELECT p.id,
       p.title,
       p.mark,
       (SELECT max(created)
          FROM comments c
          WHERE c.post_id=p.id AND c.mark=1) AS latest_at
       FROM posts p

Cakephp-3 way inner sql with the cakephp-3 querybuilder(is the query below correct?):

        $subquery = $comments->find();
        $subquery->select([$subquery->func()->max('created')])
                ->from(['c'=>'comments'])
                ->where(['c.post_id=p.id','mark=1']);

        $pquery = $postsTable->find()
        ->select(['p.id','p.title','p.mark','latest_at'=>$subquery])
        ->from(['p'=>'posts']);

How can i write the outer select query in cakephp-3 way? (select * from (derived table) )

Thanks in advance. Any answer will be highly appreciated.

Answer 1

To directly answer your question, this is how to use a subquery in the from part of another query:

$query = $this->MyTable->connection()->newQuery();
$query->select('*')->from(['Post' => $subqueryObject])->fetchAll('assoc');

This will return arrays for results, if you know what type of entity it should be hydrating to, then do this:

$query = $this->Posts
    ->find()
    ->select($listOfFields)
    ->from(['Posts' => $subqueryObject], true)
    ->toArray();

Answer 2

This is a very complicated solution to a very simple problem (i.e. getting the post with the latest comment).

A better query would be:

 select posts.* from posts join comments on (comments.post_id = posts.id) group by posts.id order by comments.created desc limit 1

The ORM way of doing this query would be:

$post = $postsTable
    ->find()
    ->select(['Posts.id', 'Posts.title', 'Posts.mark', 'Comments.created'])
    ->leftJoinWith('Comments', function ($q) {
        return $q->where(['Comments.mark' => true]);
    }))
    ->group(['Posts.id'])
    ->order(['Comments.created' => 'desc'])
    ->first();

Answer 3

For special situations, cakephp allows you to write RAW SQL and keep within the cakephp way. Just create a function in your model and you can write that query the way it is by placing it in $this->query("...") .

Example (in model):

public function getPosts(){
      $this->query("SELECT * FROM Posts AS Post"); //put complext query here
}

Then in Controller:

$posts = $this->Post->getPosts();