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

Laravel query builder efficiently handles subqueries

When using Eloquent ORM to retrieve data from subqueries, developers often use a combination of toSql() and native queries. While this approach works, it's not intuitive enough. A more efficient solution is provided here:

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

<code class="language-sql">SELECT COUNT(*) 
FROM (
  SELECT * 
  FROM abc 
  GROUP BY col1
) AS a;</code>

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

<code class="language-php">$sub = Abc::where(..)->groupBy(..);</code>

We then use DB::table to create a new table that references the subquery and manually set the corresponding bindings:

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

This approach ensures that the correct bindings are applied to the merged query to get the results we want without the need for manual string manipulation.

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