Query a nested relationship with "where" clause using Eloquent

If you've ever run into a problem where you need to filter an Eloquent model by a relationship of a relationship and also eager load both of those relationships, you know that it can get tricky to figure out.

Let's say for example that we have the following structure:

• Book belongs to Author
• Awards belong to Author

And that we need to get all the books that belong to an author that won an award this year and we want to eager load both the author and the awards won this year.

The answer

If you are busy and looking for a quick answer, here it is:

$filter = function ($query) {
    $query->where('year', now()->format('Y'));
};

$books = Book::with(['author.awards' => $filter])
    ->whereHas('author.awards', $filter)
    ->get();

Or if you are using Laravel v9.16.0 or above you can call the withWhereHas method:

$books = Book::withWhereHas('author.awards', function ($query) {
    $query->where('year', now()->format('Y'));
})->get();

If you want to know how I came up with this solution, keep reading.

Digging deeper

The first thing that might come to mind is using a constrained with in our query:

$books = Book::with(['author.awards' => function ($query) {
    $query->where('year', now()->format('Y'));
}])->get();

At a first glance, this might seem correct, but if we stop for a second and we analyze the generated SQL we can see the error.

First Laravel fetches all books:

select * from books; --see the error yet?

Then it fetches all the authors using the author_id of those books:

select * from authors where author.id in (?, ?, ...)

And finally, it retrieves the awards of those authors that are from this year:

select * from awards where awards.author_id in (?, ?, ...) and year = ?;

The books are not being filtered by our awards constraint, we get all books with their author with their awards from the current year, if the author has no award that year, then it's just an empty collection.

This is not what we want, maybe what we are looking for is a whereHas query?. Let's try that.

$books = Book::whereHas('author.awards', function ($query) {
    $query->where('year', now()->format('Y'));
})->get();

Ok, now if we check the SQL created by this query we can see it's more complicated than the previous ones:

select
  *
from
  "books"
where
  exists (
    select
      *
    from
      "authors"
    where
      "books"."author_id" = "authors"."id"
      and exists (
        select
          *
        from
          "awards"
        where
          "authors"."id" = "awards"."author_id"
          and "year" = ? 
      -- current year ☝️
      )
  )

Basically, we select only the books that have authors that have awards in the current year. Finally, this is what we want, the books are being filtered.

There is just one small problem...

We only get the books back, we are not fetching the authors or the awards.

Then why don't we combine both queries into one final one that does all that we want it to do?

$books = Book::with(['author.awards' => function ($query) {
    $query->where('year', now()->format('Y'));
}])->whereHas('author.awards', function ($query) {
    $query->where('year', now()->format('Y'));
})->get();

With this query, we filter the books and eager load the authors and awards.

But we have a little of code duplication, which is not that big of a deal here, but in real life, this query can get a lot more complicated and we might make the mistake of changing one of the queries and not the other. So let's extract that to a callback that we can use for both.

$filter = function ($query) {
    $query->where('year', now()->format('Y'));
};

$books = Book::with(['author.awards' => $filter])
    ->whereHas('author.awards', $filter)
    ->get();

By storing our closure in the $filter variable we make our query look more simple and we make sure they are always the same.

This was added recently to Laravel core, if you are using Laravel v9.16.0 or above, you can use the withWhereHas method to get the same result.

$books = Book::withWhereHas('author.awards', function ($query) {
    $query->where('year', now()->format('Y'));
})->get();

Conclusion

We did it, we solve can now filter and eager load both the relationship and the nested relationship. Now I'm going to leave you with a challenge:

Create a macro on Illuminate\Database\Eloquent\Builder that allows making the same query like this:

$books = Book::withWhereHas('author.awards', function ($query) {
    $query->where('year', now()->format('Y'));
})->get();

Here is a good article about macros that you can read if you are not familiar with them: https://tighten.com/blog/the-magic-of-laravel-macros/

The article is a bit old but everything works the same in the newest Laravel version from what I can tell.

If you manage to do it tweet at me, I'm @cosmeescobedo on Twitter, or send me an email at [email protected]. You can also do the same if you get stuck or need help with it.

Thank you for reading 👋.