PHPnews.io

★ Increase performance by using Eloquent's `setRelation` method

Written by murze.be / Original link on Aug. 4, 2022

While working on the upcoming Oh Dear redesign, I noticed that list that displays all sites of a team was very slow.

To display that list, a lot of queries were used. With a couple of minor adjustments, I could reduce the number of queries needed to just a single one, solving the performance problem.

In this small blog post, I'd like to share one of the techniques I used.

Discovering an N+1 problem

Let's look at a simplified version of how we fetch the data to build the site list for the current team.

$sites =Team::current() // returns an instance of the team model
->sites()
->get()
->filter(fn (Site $site) => currentUser()->can('administer', $site));

In the code above, you can see that we filter out the sites that the current user isn't allowed to see. We use a policy check for that, and not a simple where clause, as there is some business logic needed to decide if someone is allowed to see a site.

Now let's look at the administer method of the SitePolicy.

classSitePolicy
{
useHandlesAuthorization;

publicfunctionadminister(User $user, Site $site):bool
    {
if ($user->ownsTeam($site->team)) {
returntrue;
        }

// other rules...
    }
}

So the first rule is that if the user owns the team to which the site belongs, he/she can view the site. You can see that we call $site->team to get the team.

Getting the team there is an n+1 problem: this query is executed for each site we loop over. When displaying a site list for teams with 200 sites, 200 extra queries are performed, and this is what makes the site list slow.

From eager loading the relationship...

To solve the n+1 problem, we can eager load the relationship by using with in our query.

Here's the modified code:

$sites =Team::current() // returns an instance of the team model
->sites()
->with('team')
->get()
->filter(fn (Site $site) => currentUser()->can('administer', $site));

This successfully solves the N+1 problem. We now fetch all of the sites and teams in one query, and we don't need to execute multiple queries to fetch sites.

... to setting the relation manually

Most of you, and past me, would call it a day. We've solved the n+1 problem; now we can go home (or work on other stuff if it's not the end of the workday).

The solution above has a couple of problems. But before listing them, I want to point you to the fact that we've asked Eloquent to get all the sites for a team. So it is no surprise that all sites have the same team.

But what is logical for us as humans isn't logical for MySQL/Laravel. Here are a couple of things that are not optimal now

The problems above are not that big if you only have a few results. But if you have hundreds or thousands of results, you might notice a small performance hit.

Isn't it a shame that all this time and memory is needed to process the same team over and over again?

Luckily, we can tackle our original N+1 problem in another way that avoids all the little problems listed above. Let's take a look at our original query.

$sites =Team::current() // returns an instance of the team model
->sites()
->get()
->filter(fn (Site $site) => currentUser()->can('administer', $site));

Notice that in the code above, we already of the instance of the team model available. Let's rewrite the code, so we store the team in a separate variable.

$team =Team::current();

$team
->sites()
->get()
->filter(fn (Site $site) => currentUser()->can('administer', $site));

Now, instead of eager loading the team for each site, we will manually set the team relation of a model. This can be done using the setRelation method.

$team =Team::current();

$team
->sites()
->get()
->map(fn (Site $site) => $site->setRelation('team', $team))
->filter(fn (Site $site) => currentUser()->can('administer', $site));

By using setRelation, Eloquent will not reach out to the database anymore to get the team relation, it will simply return the Team instance we passed.

Because we didn't rely on eager loading:

In conclusion

I hope that you liked this little Eloquent performance tip. I originally heard of this method by reading Jonathan Reinink's blog post on circular relationships, and I highly recommend your reading if you want to see another example of setRelation.

Like mentioned in the intro, I'm using this technique in Oh Dear, the all-in-one monitoring tool for your entire website. Oh Dear monitors uptime, SSL certificates, broken links, scheduled tasks, application health, DNS, domain expiry and more. We send notifications when something's wrong. All that paired with a developer-friendly API and kick-ass documentation. Register to start your 10 day free trial.

murze

« Sending Spammers to Password Purgatory with Microsoft Power Automate and Cloudflare Workers KV - How the Laravel Login Link package works under the hood »