sqllaravellaravel-4eloquentquery-builder

How to select from subquery using Laravel Query Builder?


I'd like to get value by the following SQL using Eloquent ORM.

- SQL

 SELECT COUNT(*) FROM 
 (SELECT * FROM abc GROUP BY col1) AS a;

Then I considered the following.

- Code

 $sql = Abc::from('abc AS a')->groupBy('col1')->toSql();
 $num = Abc::from(\DB::raw($sql))->count();
 print $num;

I'm looking for a better solution.

Please tell me simplest solution.


Solution

  • In addition to @delmadord's answer and your comments:

    Currently there is no method to create subquery in FROM clause, so you need to manually use raw statement, then, if necessary, you will merge all the bindings:

    $sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance
    
    $count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
        ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
        ->count();
    

    Mind that you need to merge bindings in correct order. If you have other bound clauses, you must put them after mergeBindings:

    $count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    
        // ->where(..) wrong
    
        ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
    
        // ->where(..) correct
    
        ->count();