How the new 'One Of Many' Laravel relationship made my project 600 times faster

#laravel #php #webdev

When a framework is as mature as Laravel, most new features tend to become subtle improvements, "nice to haves", or something that covers an edge case that you might encounter someday... But sometimes there's a feature that makes you think "That's what I've been waiting for !".

That's precisely what happened to me when the 'One Of Many' relationships was added to Laravel 8.42 a few days ago. And I'm pretty sure I'm not the only one since it solves a pretty common problem : getting the latest item out of a One To Many relationship.

For example maybe you have a table where you write each time a user logs-in, and you want to get their latest login ? Or maybe an object that has to go through several steps in a workflow and you want to get its current status ? How convenient would it be to be able to just call $user->latestLogin() or $order->currentStatus() ?

until now it was of course doable, but it was either really tedious or had a very high performance impact. Let me give you an example

My Monitoring app

One of my current pet projects is a very simple monitoring app, that allows a user to enter a few URLs that the service will "ping" every minute, and send a text message if it becomes unavailable. The main (and only) page looks like this :

I have a grand total of 3 models in the app :

User : A Laravel user
Url : A URL to monitor (a user can have many URLs)
Check : A check that was made on a URL (a URL has many checks)

The controller for this view is really simple and the most simple approach would be something like that :

public function index(Request $request)
{
    $urls = auth()->user()->urls()->get();
    return response()->view('urls.index', ['urls' => $urls]);
}

And then in the view we create a table with each row looking like this :

@foreach($urls as $url)
    <tr>
        <td>{{ $url->name }}</td>
        <td>{{ $url->url }}</td>
        <td>{{ $url->latestCheck?->status }}</td>
    </tr>
@endforeach

And of course, I need to make latestCheck a relationship that can be queried, so I just added it in my URL model like so :

public function latestCheck(): HasOne
{
    return $this->hasOne(Check::class)->latest('id');
}

That works.

Well it kind of works. We have our results but we also have an issue called the "n+1 queries" problem, because every time we call latestCheck in the view, it will actually trigger an SQL query to get the latest check for that URL. It's very obvious when using Clockwork :

As you can see, we have 10 URLS to monitor so we make 10 SELECT queries on the checks table, but if we had 100 we'd have 100 queries which would be significantly slower. Even as it is the page takes 600ms to load which is way too long for something simple as that.

Note : The subsequent queries are faster and faster. That's because for this test I seeded the checks for each URL one after the other, so the checks for the 10th URL will be higher in the table, and be found much faster. That's not really what would happen in production.

Eager loading to the rescue !

Luckily Laravel provides us with a way to avoid this "n+1 queries" problem : eager loading. We can tell it to load all the elements of a relationships in one query by using the ->with() method like so :

$urls = auth()->user()->urls()->with('latestCheck')->get();

And sure enough, we now have only one query :

And now our page displays in... WHAAAAAAT ? 20 seconds ? And it's using 480MB of memory ? that can't be right. What's happening ?

Looking at the query, it doesn't seem to have any kind of "LIMIT" statement :

SELECT * FROM `checks` WHERE `checks`.`url_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ORDER BY `id` DESC

This will fetch ALL of the checks for those 10 URLS, and apparently Laravel is then working on a collection to extract the first check from each URL, except in my case it's a collection of 500000 items so it takes a lot of time and a lot of memory. So in our case it turns out that eager loading is actually much slower than lazy loading.

This is very unsatisfying but going back to lazy loading was our best option with Eloquent prior to Laravel 8.42. If we wanted better performance the only other way was to ditch Eloquent and write our own query with JOIN and MAX() to get the latest check.

LatestOfMany Relationship to the Rescue !

Fortunately we now have a much better solution, we can just rewrite that relationship with a small change :

public function latestCheck()
{
    return $this->hasOne(Check::class)->latestOfMany();
}

We replaced latest() with latestOfMany(), and now Laravel will make the proper SQL query behind the scene :

Now that's much better ! We went from 600ms (or 20000ms with eager loading) to less than 30ms (which is over 600 times faster than the worst case scenario), and we do have only one query that fetches just what we need :

EXPLAIN SELECT * FROM `checks`
  inner join (
    SELECT MAX(id) as id, `checks`.`url_id` FROM `checks`
    GROUP BY `checks`.`url_id`
  ) as `latestCheck`
  on `latestCheck`.`id` = `checks`.`id`
  and `latestCheck`.`url_id` = `checks`.`url_id`
WHERE `checks`.`url_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

That's really not something I'd want to write in raw SQL or the Query Builder if I don't have to, so it's really nice to have it available in one method !

Conclusion

That's all there is to it really. Whenever you want the latest occurrence of a relationship you can use this new method and it will automatically be much faster while keeping your code clean and readable !

Let us know if you've had similar experiences, or maybe a more original use case for these new relationships ?