CODEWITHSUNDEEP
phplaraveleloquent
user1684343
user1684343

Reputation: 115

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

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?

Upvotes: 3

Views: 12467

Answers (2)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

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.

Upvotes: 6

Sameer Shaikh
Sameer Shaikh

Reputation: 7814

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

Upvotes: 1

Related Questions