Avoid unnecessary work when querying relationships

Keep your database happy

Joel Clermont
Joel Clermont

Let me walk you through something dumb I (almost) did recently. I'm not afraid to share my mistakes if it helps others avoid them.

I was working in a controller that was queried by a bunch of receipt printers looking for pending jobs.

The goal was to improve reliability and performance. I saw some code that was looking to see if there was a pending job:

$pendingJob = $location->printJobs->first();
if ($pendingJob !== null) {
    // do something

The $pendingJob was only being used as a boolean. The actual model was never used for anything.

At first, I was going to do this:

if ($location->printJobs->exists()) {
    // do something

This has a bug though, since the printJobs property is a collection, and there is no exists() method on a collection.

So then my next thought was to do this:

if ($location->printJobs->isNotEmpty()) {
    // do something

It is true that isNotEmpty works on a collection, and has the same basic meaning as exists, but this is not good code.

Why? If the whole point was to avoid loading and then not using print jobs, I have made absolutely no progress on that goal. I've basically changed nothing about the query.

The SQL query run in both cases looks something like this:

select * from `print_jobs` where `print_jobs`.`location_id` = 1;

Notice how we're still querying for all the print jobs, and then Eloquent will hydrate each one of these rows into a model. All of that takes time and memory.

A much better solution is to add a filter to the relationship method:

if ($location->printJobs()->exists()) {
    // do something

Notice how I'm now using the relationship method instead of the property, so it runs a much more efficient query.

Now the SQL query looks something like this:

select exists(select * from `print_jobs` where `print_jobs`.`location_id` = 1) as `exists`;

So instead of returning all the rows from the database and unnecessarily hydrating models, my query returns a single row with a single boolean value.

This might seem like a small thing, but in my case the code was in an endpoint getting polled every couple seconds by a fleet of printers, so this small improvement actually had a significant impact.

Hope this helps,


P.S. Are you dealing with a performance issue in your Laravel app? Let's talk!

Toss a coin in the jar if you found this helpful.
Want a tip like this in your inbox every weekday? Sign up below 👇🏼

Level up your Laravel skills!

Each 2-minute email has real-world advice you can use.