Eloquent to SQL: whereHas()
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.
whereHas() method is declared on the
Illuminate\Database\Eloquent\Concerns\QueriesRelationships trait, which is exposed in the
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
Usermodel, which uses the hasMany relationship to load
- 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
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.
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)
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.
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).