Advanced Eloquent: Subqueries, Composites & Performance

· 3 min read
#laravel #eloquent #database #performance #sql #optimization

Advanced Eloquent: Subqueries, Composites & Performance

Eloquent is fantastic, but naive usage leads to sluggish applications. Developers often blame Eloquent and switch to Query Builder or Raw SQL too early. In reality, Eloquent creates highly optimized SQL if you know how to speak its advanced dialect.

This guide moves beyond simple with() usage into subqueries, dynamic ordering, and memory management.

1. The Hidden Cost of Hydration

Why is User::with('posts')->get() slow for 10,000 users? It's not just the SQL. It's Model Hydration.

Laravel has to:

  1. Run the query.
  2. Loop through every row.
  3. Instantiate a User object.
  4. Instantiate Post objects.
  5. Link them together.

If you only need to know the date of the last post, hydrating 10,000 post models is wasted CPU and RAM.

The Solution: addSelect Subqueries

Don't fetch models. Fetch data.

$users = User::addSelect(['last_post_created_at' => Post::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1)
])->paginate();

Now $user->last_post_created_at is a simple string. Zero extra models hydrated.

2. Ordering by Relationships using Subqueries

"Sort users by their latest login." This is a classic interview question.

You can't do orderBy('logins.created_at') because of the GROUP BY strict mode rules in SQL. You need a subquery.

$users = User::orderByDesc(
    Login::select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1)
)->paginate();

This generates:

select * from "users" 
order by (
    select "created_at" from "logins" 
    where "user_id" = "users"."id" 
    order by "created_at" desc limit 1
) desc

3. High-Performance Indexing (Composite Indexes)

The subquery above is fast, BUT only if you have the right index. If you have 1 million logins, that subquery runs 1 million times (conceptually).

You need a Composite Index on the child table.

// migration
$table->index(['user_id', 'created_at']); // The order matters!
  1. user_id: To narrow down to the specific user's logins.
  2. created_at: To instantly find the latest() one without detailed sorting.

Without this index, your database does a "Filesort" for every user, killing performance.

4. whereExists vs whereHas

whereHas is easy to write but often generates EXISTS (SELECT * ...) which can be slow if not optimized.

Sometimes, manually writing whereExists gives you more control, but mostly they compile to similar SQL. The key optimization is avoiding whereHas on deep relationships (e.g., whereHas('posts.comments.author')).

For deep filtering, consider denormalization (storing last_comment_at on the posts table) or using a dedicated search index (like Meilisearch).

5. Complex FROM Subqueries (fromSub)

Sometimes you need to aggregate data before you join it.

Scenario: Get the total revenue per user, but only for users who spent > $1000.

Naive way: Group by in the main query (can't paginate easily). Better way: fromSub.

$totals = DB::table('orders')
    ->selectRaw('user_id, sum(amount) as total_spent')
    ->groupBy('user_id');

$highValueUsers = User::joinSub($totals, 'totals', function ($join) {
        $join->on('users.id', '=', 'totals.user_id');
    })
    ->where('totals.total_spent', '>', 1000)
    ->select('users.*', 'totals.total_spent')
    ->paginate();

This creates a clean derived table in SQL, allowing Laravel to paginate the results correctly.

Conclusion

  1. Avoid Hydration: Use addSelect for single values.
  2. Order Smart: Pass subquery builders to orderBy.
  3. Index Correctly: Subqueries are useless without composite indexes.
  4. JoinSub: Use derived tables for complex aggregation needs.

Eloquent is a tool. Like a race car, you need to know how to shift gears to win.

Comments