3

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?

user1684343
  • 115
  • 1
  • 2
  • 7
  • My answer to this [question](http://stackoverflow.com/a/27911214/3583182) should help you out. As I mention in the comments, I'll provide some updated code tomorrow which will let you add constraints to the count statement (e.g. your "approved = true"). This does, however, require that the relationships be setup correctly. – patricus Jan 19 '15 at 07:29
  • @patricus You're trying too hard, better read this http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently – Jarek Tkaczyk Jan 19 '15 at 10:12
  • @JarekTkaczyk It depends on what the need is. I definitely like your approach on using the relationships, and would use that approach in other situations, but it doesn't exactly answer the question as asked. There are situations where one may need the count of a relationship added to the initial SQL statement, rather than just having the count readily available once the model is loaded. For example, db server ordering the results based on the count, such as in the datatables issue to which I pointed in my comment above. – patricus Jan 20 '15 at 02:07
  • @patricus Yep, that's different story and relates only to datatables (or ordering by relation in general) which is not the case here. – Jarek Tkaczyk Jan 20 '15 at 07:25
  • @JarekTkaczyk No, ordering by the count is not the case here. However, all we know from the question is that the user wanted the count as part of the initial SQL statement. If that is a constraint that must be followed, then your solution won't work. If that is a constraint that can be relaxed, then your solution is great. – patricus Jan 20 '15 at 21:39
  • @patricus I have no idea where you took ordering by count from. I never mentioned it, neither did the OP. Anyway, the question is *what is the proper way of doing it with eloquent*, and the link gives exact answer to the question. I don't think there's any need for us 2 to continue, but rather OP's job to decide what he needs. – Jarek Tkaczyk Jan 20 '15 at 21:52
  • @JarekTkaczyk The question is _what is the proper way to get eloquent to produce **the query** that generates those results_. The link does not give the exact answer, because it generates multiple queries to get those results. It is a great solution if that is okay. It is not the solution if the OP specifically needs one query. Frustratingly, text as a medium is toneless. I'm not trying to be combative at all, just trying to express why my answer differs from yours. As I mentioned, I really like your answer for the problem it solves. But yes, the OP has an answer for either situation. – patricus Jan 20 '15 at 22:10

2 Answers2

6

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.

Community
  • 1
  • 1
Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
1

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.

$users = Blog::with(array('Comment' => function($query)
{
    $query->
    where('approved','=',true)->
    select(DB::raw('Count(comment_id) as comment_count'));

}))->get();
Sameer Shaikh
  • 7,564
  • 2
  • 17
  • 32