Laravel: Proper way to get Eloquent to create nest

Question

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?

Answer 1

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.

Answer 2

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();