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.