How to Efficiently Select from Subqueries Using Laravel's Query Builder?

Select data from subqueries using the Laravel query builder

Question: Retrieving the value of a count aggregate from a SQL subquery using Eloquent ORM.

Initial method:

$sql = Abc::from('abc AS a')
    ->groupBy('col1')
    ->toSql();

$num = Abc::from(\DB::raw($sql))
    ->count();

This approach requires manual generation of subquery SQL, which is not ideal.

Best solution:

The Laravel query builder currently lacks a dedicated method for creating subqueries in the FROM clause. Raw statements must be used manually, with proper binding management:

// 定义子查询
$sub = Abc::where(...)->groupBy(...); // Eloquent Builder 实例

// 创建主查询
$count = DB::table(DB::raw("({$sub->toSql()}) AS sub"))
    ->mergeBindings($sub->getQuery()) // 正确合并绑定
    ->count();

Note: Bindings must be merged in the correct order. If additional conditions are added after merging, the order must be adjusted to ensure correct binding.

The above is the detailed content of How to Efficiently Select from Subqueries Using Laravel's Query Builder?. For more information, please follow other related articles on the PHP Chinese website!