Last Updated:

Using Laravel subqueries

Hello. It's been a long time since I wrote articles on my blog. Lots of work and little time. In this article, we'll look at how to use subqueries in Laravel. Let's say we have two tables: users and comments. The users table is related to the hasMany table to the comments table. And we want to bring out all users with pagination (10 each) and show the date of the last comment to the user. But there are a few problems...

 

And so, there are several problems:

  1. N+1 запрос
  2. A lot of memory is used.

Then we will solve these problems.

Pagination in Laravel is very simple. To do this, use the paginate($el) method. What is pagination and how to implement it you can read in the article "Laravel pagination". And the article "Laravel Ajax pagination" describes how to implement ajax pagination in Laravel.

We digressed a bit. Let us meet our challenges. And the first thing that comes to mind is to use greedy or lazy loading. What it is you can read in the article "Greedy and lazy loading in Laravel. With() and load()" methods.

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

Thus we solved the first problem of the N+1 request, but the second problem remained. After all, imagine if we have 50 users and each left an average of 100 comments. Then 5000 entries will be uploaded!!!

The second problem with the use of large amounts of memory remained. This is where sub-queries in Laravel come to our rescue.

Subqueries in Laravel

Essentially, Laravel subqueries allow you to select additional columns or attributes in a primary table query.

$lastComment = Comment::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1)
    ->getQuery();
$users = User::select('users.*')
    ->selectSub($lastComment, 'last_comment_at')
    ->get();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($user->last_comment_at)
                {{ $user->last_comment_at->format('M j, Y \a\t g:i a') }}
            @else
                Нет комментариев
            @endif
        </td>
    </tr>
@endforeach

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

If we consider what sql query we got, we will see the following:

select
    "users".*,
    (
        select "created_at" from "comments"
        where "user_id" = "users"."id"
        order by "created_at" desc
        limit 1
    ) as "last_comment_at"
from "users"

In this way, we solved our two problems: we got rid of the N+1 request and minimized the memory usage.

Subquery Laravel in scope

Laravel subqueries can be used in Scope. Open the User model and add the following method to it:

function scopeWithLastCommentDate($query)
{
    $query->addSubSelect('last_comment_at', Comment::select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
    );
}

And now we can use:

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

Thank you.