Laravel:让 Eloquent 创建嵌套 SELECT 的正确方法 [英] Laravel: Proper way to get Eloquent to create nested SELECT

问题描述

我试图雄辩地生成的查询是

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

这是结果

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()) 运行它，这可能不是正确的方法.

I currently have this running by using DB::select( DB::raw()) which probably isn't the correct way to do this.

问题是什么才是能够产生这些结果的查询的正确方法?

The question is what is the proper way to get eloquent to produce the query that generates those results?

推荐答案

改用这个:http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-高效

对于嵌套的 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中.

Or simply put everything in selectRaw.

这篇关于Laravel:让 Eloquent 创建嵌套 SELECT 的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！