Eloquent Performance: Subqueries

In the previous post, I asked the following question:

How can you get the latest login date from App\Models\User in an efficient way?

The short answer is by using subqueries.

A subquery is a query within another query, and it is supported by most RDBMS such as MySQL, Postgres etc.

Here is an example:

SELECT
    `users`.*,
    (
        SELECT
            COUNT(id)
        FROM
            `posts`
        WHERE
            `user_id` = `users`.`id`
        LIMIT 1) AS `total_posts`
FROM
    `users`
ORDER BY
    `total_posts` DESC
LIMIT 15 OFFSET 0

The above query counts the total number of posts for each user, so you do not need a separate SELECT COUNT()... statement.

Laravel supports subqueries through the addSelect method:

$users = User::query()
    ->addSelect([
        'total_posts' => Post::selectRaw('COUNT(*)')
            ->whereColumn('user_id', '=', 'users.id')
            ->take(1)
    ])
    ->orderBy('total_posts', 'desc')
    ->paginate();

Remember to join the tables correctly when using subqueries.

You now have a new total_posts column on the App\Models\User -- all from a single query.

Move your subqueries into scopes for better organization and reusability:

// App\Models\Post

public function scopeWithTotalPosts()
{
    $users = User::query()
        ->addSelect([
            'total_posts' => Post::selectRaw('COUNT(*)')
                ->whereColumn('user_id', '=', 'users.id')
                ->take(1)
        ])
        ->orderBy('total_posts', 'desc')
        ->paginate();
}

You can call the scope like this:

User::withTotalPosts()->paginate();

#Getting the latest login date using subquery

Now that you know how to use subqueries in Laravel, you can apply the same technique to get the latest login date.

Here is how:

// App\Models\User

public function scopeWithLatestLoginDate($query)
{
    $query->addSelect(['latest_login_date' => Login::select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
        ])
        ->withCasts(['last_login_at' => 'datetime']);
}

// App\Http\Controllers\UsersController

$users = User::query()
    ->withLatestLoginDate()
    ->paginate(10);

Now, you can access the latest_login_date directly from the App\Models\User:

@foreach ($users as $user) {
    {{ $user->last_login_at->diffForHumans() }}
}

The latest_login_date is an instance of Illuminate\Support\Carbon because it was cast using the withCasts method.

In the next upcoming post, I will discuss fake relationships using subqueries.

#Summary

• Subqueries eliminate extra queries -- instead of running separate queries for aggregated or derived data, embed them directly into your main query using addSelect.
• Move subqueries into scopes -- wrapping subqueries in Eloquent scopes keeps your controllers clean and makes the logic reusable across the application.
• Cast subquery results -- use withCasts to automatically convert subquery values (like dates) into proper types such as Carbon instances.