Laravel：让 Eloquent 创建嵌套 SELECT 的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28018466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel: Proper way to get Eloquent to create nested SELECT
提问by user1684343
The query I am trying to get eloquent to generate is
我试图雄辩地生成的查询是
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
This is the result
这是结果
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
I currently have this running by using DB::select( DB::raw()) which probably isn't the correct way to do this.
我目前使用 DB::select(DB::raw()) 运行它，这可能不是正确的方法。
The question is what is the proper way to get eloquent to produce the query that generates those results?
问题是什么是正确的方法来雄辩地生成生成这些结果的查询？
回答by Jarek Tkaczyk
Use this instead: http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently
改用这个：http: //softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently
And for nested select/joinstatement, you need this:
对于嵌套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();
Or simply put everything in selectRaw.
或者干脆把所有东西都放进去selectRaw。
回答by Sameer Shaikh
You can use laravel ELoquent with eager loading I suggest you study about laravel relationship to get full advantage of laravel By the way once you have defined relationship between these two models, the below code might work for you.
你可以使用 laravel ELoquent 和预先加载我建议你研究 laravel 关系以充分利用 laravel 顺便说一下，一旦你定义了这两个模型之间的关系，下面的代码可能对你有用。
$users = Blog::with(array('Comment' => function($query)
{
$query->
where('approved','=',true)->
select(DB::raw('Count(comment_id) as comment_count'));
}))->get();
