Eloquent to SQL: whereHas()

Preface

I previously wrote about the has() method in Eloquent. This post will be following on very closely from that one, so it may be helpful to give that a read first, as some assumptions in this post will be made with it in mind.

Eloquent to SQL: has()


Laravel API

The whereHas() method is declared on the Illuminate\Database\Eloquent\Concerns\QueriesRelationships trait, which is exposed in the Illuminate\Database\Eloquent\Builder class.

What Does It Do?

Similarly to the has() method, the whereHas() method allows us to fetch database records, where a given relationship meets the given conditions. The whereHas() method is actually more of a convenience method which under the hood will delegate to the has() method. It does however have the additional benefit of making our code more explicit, and increase readability. It will also rearrange the parameters to allow us to pass values to it in a more convenient order.

Consider this example

  • You have a User model, which uses the hasMany relationship to load Task's.
  • You wish to display a list of users who have tasks which are overdue.

The PHP code to achieve this may look something like this:

Although this code will work, it isn’t (at least in my eyes) very readable. It also means that we will be loading all users and their tasks into memory, regardless of whether we actually need to use them within the rest of the request.

How could we push this logic down to the database level…

Let’s look at the code below to see how we can use the whereHas() method to update our database query, so that we only fetch users who have overdue tasks.

It’s worth pointing out, that the closure here will be passed an instance of the full Eloquent query builder, so you will have access to all the usual Eloquent methods, including any custom scopes you have have added to the model of the relation — in this case the Task model.

How Does It Work?

Similarly to the has() method, this is the query which Eloquent will run for us.

You should notice that in this case, an additional WHERE clause has been added to the sub query which looks for existence of our relationship. This now means that a task will only be classed as having existence, if it's due date has passed. As such, only users who have tasks with passed due dates will be returned.

Additional Parameters

As I previously mentioned, the whereHas() method simply delegates to the has() method with the parameters in a different order. As such, this means that the same additional parameters may be passed to the whereHas() method to further define what classes as existence. (e.g. only fetch users who have 2 or more tasks overdue)

Method Variations

As with most things in Laravel, there are of course a number of more declarative methods which you can use to make variations of whereHas more readable.

Conclusion

So in closing, whereHas() is a great way to ensure you are only fetching data from the database that you actually need, and allows you to do that based on conditions of a relationship.

There will be some cases where these methods are not the most performant, and in some cases you may wish to favour joins. This is simply due to the performance of subqueries within SQL. However from my experience, for the most part, the benefit of having a very declarative and readable code base will outweigh the slight performance hit (which will of course vary on a case-by-case basis).


One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.