How to Efficiently Count Records from a Laravel Subquery with GROUP BY?

Subquery selection in Laravel query builder

Selecting data from a subquery using the Eloquent ORM's query builder can be challenging. This article addresses a specific issue faced by a user who wanted to obtain the record count for a subquery created using GROUP BY.

Dive into the questions:

Users initially tried to resolve this issue using the following code:

<code class="language-php">$sql = Abc::from('abc AS a')
    ->groupBy('col1')
    ->toSql();
$num = Abc::from(DB::raw($sql))
    ->count();
print $num;</code>

While this approach seems logical, it is not the most efficient solution.

Elegant and optimized solution:

The suggested solution involves using a combination of raw SQL and bind merging:

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

$count = DB::table(DB::raw("({$sub->toSql()}) as sub"))
    ->mergeBindings($sub->getQuery()) // 以正确的顺序合并绑定
    ->count();</code>

In this solution, the subquery is built using an Eloquent Builder instance and then included in a raw SQL expression. The mergeBindings() method is used to combine the bindings of a subquery with the bindings of the main query. This way you can efficiently retrieve the required data from the subquery.

The above is the detailed content of How to Efficiently Count Records from a Laravel Subquery with GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!