Laravel:让 Eloquent 创建嵌套 SELECT 的正确方法

2021-12-26 00:00:00 php laravel eloquent

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

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.

相关文章