Laravel：正确的方法来获得雄辩的创建嵌套的SELECT [英] Laravel: Proper way to get Eloquent to create nested SELECT

问题描述

  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 |标题|作者|博客|图像|标签|创建|更新| comment_count 
 -------- | ------------------- | -------------- |  - --------------- | ------------------ | --------- | ----- ---------------- | --------------------- | ----------- --- 
 21 |一天.. | dsyph3r | Lorem ipsum ... | beach.jpg | symf ... | 2014-12-22 19:14:34 | 2014-12-22 19:14:34 | 2 
 22 |游泳池.. |零酷|前卫... | pool_leak.jpg |池，.. | 2011-07-23 06:12:33 | 2011-07-23 06:12:33 | 10 
 23 |误导... |加百列| Lorem ipsum ... | misdirection.jpg |误会... | 2011-07-16 16:14:06 | 2011-07-16 16:14:06 | 2 
 24 |网格... |凯文·弗林Lorem commo ... | the_grid.jpg |网格... | 2011-06-02 18:54:12 | 2011-06-02 18:54:12 | 0 
 25 |你是... |加里·温斯顿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

对于嵌套的选择 / 加入语句，需要这样： p>

  $ sub =注释:: 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 中。 / p>

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.

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

解决方案

Use this instead: http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently

And for nested select/join statement, you need this:

$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.

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