Laravel query constructor efficiently handles subqueries

When using Eloquent ORM to retrieve data from subqueries, developers often use toSql() and native queries in combination. Although this method is effective, it is not intuitive enough. Here is a more efficient solution:

For example, to extract the result count from the following subquery:

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

Laravel allows us to merge native queries into Eloquent queries using mergeBindings. First, we create an Eloquent Builder instance for the subquery:

$sub = Abc::where(..)->groupBy(..);

Then, we use DB::table to create a new table that references the subquery and manually set the corresponding binding:

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery()) // 获取底层查询构造器
    ->count();

This approach ensures that the correct binding is applied to the merged query, resulting in the result we want without manually performing string operations.