How to choose from a subquery using Laravel Query Builder?

Question

I would like to get the value for the following SQL using Eloquent ORM.

- SQL

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

Then I considered the following.

- The code

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

I am looking for the best solution.

Please tell me the simplest solution.

Answer 1

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

There is currently no way to create a subquery in FROM , so you need to manually use the raw command, and then, if necessary, you 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(); 

Remember that you need to combine the bindings in the correct order . If you have other related sentences, you should 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(); 

Answer 2

@JarekTkaczyk's solution is exactly what I was looking for. The only thing I missed is how to do this when you use DB::table() queries. In this case, I do this:

 $other = DB::table( DB::raw("({$sub->toSql()}) as sub") )->select( 'something', DB::raw('sum( qty ) as qty'), 'foo', 'bar' ); $other->mergeBindings( $sub ); $other->groupBy('something'); $other->groupBy('foo'); $other->groupBy('bar'); print $other->toSql(); $other->get(); 

Particular attention to how to make mergeBindings without using the getQuery() method

Answer 3

I like to do something like this:

 Message::select('*') ->from(DB::raw("( SELECT * FROM `messages` WHERE `to_id` = ".Auth::id()." AND `isseen` = 0 GROUP BY `from_id` asc) as `sub`")) ->count(); 

It is not very elegant, but simple.

Answer 4

I could not get your code to make the desired query, AS is an alias only for the abc table and not for the derived table. Laravel Query Builder does not imply support for derived table aliases; DB :: raw is likely to be needed for this.

The most direct solution I could come up with is almost identical to yours, but it produces a request, as you requested:

 $sql = Abc::groupBy('col1')->toSql(); $count = DB::table(DB::raw("($sql) AS a"))->count(); 

Query Generated

 select count(*) as aggregate from (select * from `abc` group by `col1`) AS a; 

Answer 5

Do you need a sub request? You can try something like

$num = Abc::groupBy('col1')->get()->count();

Not ideal if you have millions of lines.