我试图雄辩地生成的查询是
SELECT *, (SELECT COUNT(comment_id) FROM comment AS c WHERE c.approved=true AND c.blog_fk=b.blog_id) AS comment_count FROM blog AS b
这是结果
blog_id | title | author | blog | image | tags | created | updated | comment_count
--------|-------------------|--------------|----------------|------------------|---------|---------------------|---------------------|--------------
21 | A day.. | dsyph3r | Lorem ipsum... | beach.jpg | symf... | 2014-12-22 19:14:34 | 2014-12-22 19:14:34 | 2
22 | The pool .. | Zero Cool | Vestibulum ... | pool_leak.jpg | pool,.. | 2011-07-23 06:12:33 | 2011-07-23 06:12:33 | 10
23 | Misdirection... | Gabriel | Lorem ipsum... | misdirection.jpg | misd... | 2011-07-16 16:14:06 | 2011-07-16 16:14:06 | 2
24 | The grid ... | Kevin Flynn | Lorem commo... | the_grid.jpg | grid... | 2011-06-02 18:54:12 | 2011-06-02 18:54:12 | 0
25 | You're either ... | Gary Winston | Lorem ipsum... | one_or_zero.jpg | bina... | 2011-04-25 15:34:18 | 2011-04-25 15:34:18 | 2
我目前使用 DB::select( DB::raw()) 运行它,这可能不是执行此操作的正确方法。
问题是,雄辩地生成生成这些结果的查询的正确方法是什么?
改用这个: http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently
对于嵌套的select
/join
语句,您需要这个:
$sub = Comment::selectRaw('count(comment_id) as count')
->where('approved', '?')
->where('comment.blog_fk', '?')
->toSql();
Blog::selectRaw(DB::raw("blog.*, ({$sub}) as comment_count"))
->setBindings([true, DB::raw('blog.blog_id')], 'select')
->get();
或者干脆把所有东西都放在selectRaw
.
您可以使用 laravel ELoquent 与预先加载我建议你研究拉拉维尔关系,以充分利用拉拉维尔顺便说一下,一旦您定义了这两个模型之间的关系,下面的代码可能适合您。
$users = Blog::with(array('Comment' => function($query)
{
$query->
where('approved','=',true)->
select(DB::raw('Count(comment_id) as comment_count'));
}))->get();