汇总关系的雄辩查询
问题描述:
我想通过模型关系的汇总值进行查询。 举例来说,我应该只会得到的帖子它有两个日期之间的最后一条评论。汇总关系的雄辩查询
SELECT posts.*, MAX(comments.created_at)
as max FROM posts
JOIN comments ON (comments.post_id = posts.id)
GROUP BY posts.id HAVING max > '2014-01-01 00:00:00' AND max < '2014-02-01 00:00:00'
答
取而代之的连接使用内置的方法:
// Assuming you have relations setup
Post::whereHas('comments', function ($q) use ($from, $till) {
$q->groupBy('post_id')
->havingRaw("max(created_at) between '{$from}' and '{$till}'");
})->get();
它会产生:
select * from `posts` where
(select count(*) from `comments` where `comments`.`post_id` = `posts`.`id`
group by `post_id`
having max(created_at) between '2014-01-01' and '2014-02-01'
) >= 1 limit 1