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