Improving Eloquent Subquery Performance in Reimpact Platform

Introduction

We were experiencing slow query times in the Reimpact platform when using Eloquent subqueries with anonymous models. The issue stemmed from unintended default ordering and a mismatch between the model table and the subquery alias.

The Problem

Eloquent was automatically adding an ORDER BY pulse_entries.id clause, even though pulse_entries.id didn't exist in the subquery's context. This default ordering was unnecessary and slowing down the query execution. Additionally, the anonymous model's table name wasn't correctly aligned with the subquery alias, causing further confusion for the query builder.

The Solution

To address this, we took two key steps:

1. Align the anonymous model's table with the subquery alias: This ensures that Eloquent correctly references the subquery's result set.
2. Remove the default ordering: By calling reorder(), we prevent Filament's default key ordering from being applied to the subquery.

$subquery = PulseEntry::query()
    ->select(['key', DB::raw('count(*) as count')])
    ->groupBy('key')
    ->orderByDesc('count');

$anonymousModel = new class extends Model
{
    protected $table = 'your_subquery_alias'; // Align table name
};

$result = $anonymousModel::fromSub($subquery, 'your_subquery_alias')
    ->reorder() // Remove default ordering
    ->get();

Explanation

• PulseEntry::query() initiates an Eloquent query on the PulseEntry model.
• select(['key', DB::raw('count(*) as count')]) selects the 'key' and a count of each key, aliased as 'count'.
• groupBy('key') groups the results by the 'key' column.
• orderByDesc('count') orders the results in descending order based on the count.
• $anonymousModel = new class extends Model... creates an anonymous model, extending the base Eloquent Model.
• protected $table = 'your_subquery_alias'; sets the table name for the anonymous model to match the subquery alias.
• $anonymousModel::fromSub($subquery, 'your_subquery_alias') creates a query builder instance from the subquery, assigning it the alias 'your_subquery_alias'.
• ->reorder() removes any default ordering that might be applied by Eloquent or Filament.
• ->get() executes the query and retrieves the results.

Benefits

By aligning the model table and removing the default ordering, we observed a significant improvement in query performance, especially for complex subqueries. This optimization ensures that Eloquent doesn't introduce unnecessary overhead when working with subqueries and anonymous models.

Key Takeaway

When using Eloquent subqueries with anonymous models, always verify that the model's table name matches the subquery alias and explicitly remove any default ordering to avoid performance bottlenecks.