Eloquent Subqueries: Tips & Tricks

Tip: Subquery in Select

Post::addSelect([
    'latest_comment' => Comment::select('body')
        ->whereColumn('post_id', 'posts.id')
        ->latest()
        ->limit(1),
])->get();

Adds a computed column without a join.

Gotcha: whereColumn() for Same-Table Comparisons

Post::whereColumn('updated_at', '>', 'created_at')->get();

Compares two columns in the same row.

Tip: Subquery Where

User::whereHas('posts', fn($q) => $q->where('published', true))->get();

Generates an EXISTS subquery.

Tip: whereExists() for Complex Conditions

User::whereExists(function ($query) {
    $query->select(DB::raw(1))
        ->from('posts')
        ->whereColumn('posts.user_id', 'users.id')
        ->where('posts.published', true);
})->get();

Gotcha: havingRaw() for Aggregate Filters

User::select('users.*')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->groupBy('users.id')
    ->havingRaw('COUNT(posts.id) > ?', [5])
    ->get();

Tip: Cross-Database Subqueries

$latestOrder = DB::connection('mysql2')
    ->table('orders')
    ->select('id')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1);

User::addSelect(['last_order' => $latestOrder])->get();

Gotcha: Subqueries Don't Work with All Drivers

SQLite has limited subquery support in SELECT clauses. Test on your production database.

Tip: Use cursor() for Memory-Neutral Iteration

When exporting 100K rows, get() loads everything into memory. cursor() uses yield and keeps memory flat regardless of row count. Perfect for artisan commands.

Tip: whereHas() vs load() — Two Different Things

whereHas() filters the parent query by relationship existence. load() eager-loads relationships AFTER the query. Mixing them up is a common source of logic bugs.

Gotcha: withCount() Adds a Subquery

withCount('comments') runs a correlated subquery on every row. On large tables, this can be slower than a separate query. Profile before relying on it.

Senior Insight

Eloquent is Laravel's most controversial feature — developers either love its convenience or hate its magic. My take after years of using both Eloquent and raw SQL: Eloquent is excellent for 80% of queries and wrong for the other 20%. The skill is knowing which is which. If a query involves more than three joins, window functions, or complex subqueries, reach for the Query Builder or raw SQL. Eloquent's beauty is in its simplicity for CRUD; fighting it for complex queries helps no one.

Source: Laravel Docs (https://laravel.com/docs/eloquent), Laravel News (https://laravel-news.com/), Freek.dev (https://freek.dev/tags/eloquent)