Skip to content

Deep Diving into Laravel Sub-query

Posted on:December 26, 2018 at 10:00 AM

In this tutorial, we will learn how to use Laravel Subquery in real life example. I have inspired to write about this article by reading Jonathan Reinink post. A big clap for him.

Imagine that, you have two tables, users and posts where users table hasMany() relationship with posts table. Now, we would love to show all users paginate by 10 and show their last post time. In this example, we will try to overcome the following challenges-

The first idea comes in mind that, we can easily use relationship with eager loading. No doubt, yes, we can do that. Let’s do.

$users = User::with('events')->paginate(10);

return view('home', compact('users'));

Sure enough, it will show the data in the view.

Cool. But if you notice that, it has solved the N+1 issue, however, the memory size has been increased. For example-

So, it doesn’t serve our purpose. So, let’s dig into the other way, called Laravel Subqueries.


Basically, Laravel subqueries allow selecting extra columns or attributes in the primary table query.

$lastPost = Post::select('created_at')
    ->whereColumn('user_id', '')

$users = User::select('users.*')
    ->selectSub($lastPost, 'last_post_at')

@foreach ($users as $user)
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
            @if ($user->last_post_at)
                {{ $user->last_post_at->format('M j, Y \a\t g:i a') }}
                No post yet

{{ $users->paginate(10) }}

If we deep look into this query, we will get this-

        select "created_at" from "posts"
        where "user_id" = "users"."id"
        order by "created_at" desc
        limit 1
    ) as "last_post_at"
from "users"

This will get the required information for users page by a single query. No doubt, increase the performance drastically where it keeps both the N+1 and the minimizing the memory usage. Cool, right?

Using subquery in query scope

It is also possible to use a subquery in the Laravel query scope. Let’s convert subquery into a scope on the User model-

class User extends Model
    public function scopeWithLastPostDate($query)
        $query->addSubSelect('last_post_at', Post::select('created_at')
            ->whereColumn('user_id', '')

$users = User::withLastPostDate()->get();

Nice, right?

Using Macro in query scope

There is a way to set a macro to keep the subquery boilerplate that will reduce your effort too. A new addSubSelect method will be added to the query builder. Add the following code in your AppServiceProvider.

use Illuminate\Database\Query\Builder;

Builder::macro('addSubSelect', function ($column, $query) {
    if (is_null($this->columns)) {

    return $this->selectSub($query->limit(1), $column);

This macro basically do-

Finally, let’s write the code to use this new macro:

$users = User::addSubSelect('last_post_at', Post::select('created_at')
    ->whereColumn('user_id', '')


So, let’s stop here. If you have any query/opinion/suggestion, let’s discuss to improve that.

Thank you.