Laravel Eloquent SubQuery and SubSelect: Quick Examples

Working with complex MySQL queries often involves subqueries and subselects. Let's see how to implement them in Laravel and Eloquent.

Did you know that you can accomplish the query below with Eloquent:

select `users`.*
from `users`
where (select count(*) from posts where posts.user_id = users.id) >= 5

Let me show you the options.

Writing a SubSelect

There are a couple of ways you can write subselects in Eloquent. The first one allows you to use models:

$users = User::query()
    // Adding this will attempt to add a sub-select
    ->addSelect([
        // Key is the alias, value is the sub-select
        'lastPost' => Post::query()
            // You can use eloquent methods here
            ->select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
    ])
    ->get();

That's it. This will add a subselect to the query. You can then access it like this:

foreach ($users as $user) {
    echo $user->lastPost;
}

Another way is to use a raw expression:

use Illuminate\Support\Facades\DB;
 
// ...
 
$users = User::query()
    ->select('users.*')
    ->addSelect(DB::raw('(SELECT created_at FROM posts WHERE posts.user_id = users.id ORDER BY created_at DESC LIMIT 1) as lastPost'))
    ->get();

Then you can access it like this:

foreach ($users as $user) {
    echo $user->lastPost;
}

Both methods are valid, but the first one is easier to use as it uses models, and you can use Eloquent methods.

Writing SubQueries

Subqueries are more complex but still doable. Let's say you want all users with at least five posts. You can do it like this:

$users = User::query()
    ->select('users.*')
    ->whereRaw('(select count(*) from posts where posts.user_id = users.id) >= 5')
    ->get();

This will add a subquery to the WHERE clause:

select `users`.*
from `users`
where (select count(*) from posts where posts.user_id = users.id) >= 5

Of course, you can use the DB facade to write the query as well:

use Illuminate\Support\Facades\DB;
 
// ...
 
$users = User::query()
    ->select('users.*')
    ->where(DB::raw('(select count(*) from posts where posts.user_id = users.id)'), '>=', 5)
    ->get();

This will produce the same query as above. Yet, it uses ->where() instead of ->whereRaw().

And while it's similar to the subselect, it's not the same. You can only access the data where it's compared.

One More Thing

When dealing with Raw DB expressions - be careful with parameters. You can easily make a mistake and introduce an SQL injection vulnerability. So, make sure you know what you're doing.