Deep Diving into Laravel Sub-query

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-

• Minimizing database queries
• Minimizing memory usage

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-

• Users per page => 50 users
• Average Posts per user => 250 Posts
• Total Posts records loaded => 12,500 records

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

Subqueries

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

$lastPost = Post::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1)
    ->getQuery();

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

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

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

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

select
    "users".*,
    (
        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.id')
            ->latest()
        );
    }
}

$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)) {
        $this->select($this->from.'.*');
    }

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

This macro basically do-

• Add a select('table.*') to get the base table columns and the new subquery column. It is required, while the Laravel won’t include a select *.
• Add the limit of one because of subqueries can able to return a single row only.
• Call the selectSub method.

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

$users = User::addSubSelect('last_post_at', Post::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
)->get();

Cool.

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

Thank you.