Querying Relations

As I have stated before, relationships are a big feature of Eloquent; It is one of the strongest features of Eloquent. This allows for you to bring the relationships between database records into the realm of being relationships for your models. Since your models are object representations of records this just continues that trend.

Note: To continue with this article you should know how to define relationships and have a decent understanding of how to call them. You can refer to the Eloquent Relationship docs for this information.

You may find my previous article Eloquent - People's Misunderstandings helpful as it goes over some things not covered in here such as: the dynamic property for relationships and the differences between using that and the relationship methods.

Laravel Docs - Eloquent - Relationships

Querying Relations
Eager Loading
Lazy Eager Loading
Constraining Eager Loading
Just getting a count
Querying based on existence of a relationship
Now lets add some conditions
Nested Relationships
Know your queries
Conclusion

Eager Loading

What a fantastic feature, "eager loading". This helps us remove the N+1 problem of what lazy loading would cause. You can load an entire set of relationships for your models without having to query for each relationship for each model separately.

An example of N+1 would be something like this:

$users = User::all();

foreach ($users as $user) {
    echo $user->profile->name;
}

This will cause a new query to be executed to load the profile relationship on each $user as we iterate through this loop. That is lazy loading and will cause a lot of queries potentially.

$users = User::with('profile')->get();

foreach ($users as $user) {
    echo $user->profile->name;
}

This is eager loading the relationship for all the users in that set via 1 query to get all the related records and attach them to the associated user model. This will only cause 2 queries, one to get the User records and one query to then fetch the Profile records using the User ids. This is way more efficient and on a large result set would make a very large difference.

Laravel Docs - Eloquent - Relationships - Querying Relationships - Eager Loading

Lazy Eager Loading

This is a gem that people often dont realize exists. If you already have an existing model instance or Collection of models but didn't eager load any relationship, you can do it after the fact.

$users = User::all();

// down the line: ah I need their profiles

$users->load('profile');

You now have lazy eager loaded the Profile records for each model instance via a single query. Now when you access them there will not be any new queries as the relationship has been loaded for each of the models in that Collection.

This can be quite handy when dealing with the Auth User. I see a lot of people do things like this:

$user = User::with('profile')->find(Auth::user()->id);

This is completely unneeded for 2 main reasons (1 and 2-3).

Auth::user() is a User record already, you don't have to query for it again.
You can just hit the dynamic property to do the same loading (lazy loading).
```
 Auth::user()->profile;
```
If you wanted to you could lazy eager load it. (Though since this isn't a set of records it isn't going to decrease the queries compared to the lazy loading of the dynamic property above.)
```
 Auth::user()->load('profile');
```

Laravel Docs - Eloquent - Relationships - Querying Relations - Lazy Eager Loading

Constraining Eager Loading

You can always constrain how a relationship will be eager loaded. Perhaps you don't want all the records eager loaded, but only ones that meet a certain criteria.

User::with(['comments' => function ($q) {
    $q->where('created_at', '>=', \Carbon\Carbon::now()->subWeek());
}])->get();

We are loading the comments relationship for all the returned Users, but we are limiting it to only Comments that were created in the last week.

Laravel Docs - Eloquent - Relationships - Querying Relations - Constraining Eager Loads

Just getting a count

There has been a new addition to Eloquent which will allow you to get a count of a relationship without actually loading all the corresponding models.

$users = User::withCount('comments')->get();

foreach ($users as $user) {
    echo $user->comments_count;
}

This method can be used with constraints as well, so don't be afraid to use it similarly to with() when you want to constrain it.

This is adding a {relation}_count column to the corresponding models for you to access.

Laravel Docs - Eloquent - Relationships - Query Relations - Counting Relationship Results

Querying based on existence of a relationship

This is a useful ability. To put it in a fluent way of saying it, we want User records that 'have' comments. Or to be more in tune with the method name we will use, we want a User that 'has' comments. (We are filtering the result set, Users, by a relationship)

User::has('comments')->get();

We are getting all Users that have any Comments via the comments relationship.

Lets say we want all Users who have at least 10 comments:

User::has('comments', '>=', 10)->get();

That will give us all users that have at least 10 comments.

There is an inverse for the has method: doesntHave.

Laravel Docs - Eloquent - Relationships - Query Relations - Querying Relationship Existence

Note: This is a competely separate thing from eager loading. This is constraining the base result set on an existance of a relationship. Eager load is just loading a relationship for all of the result set. You can use conditions on both but they are for different purposes.

Now lets add some conditions

You will often be in a position where a simple count of relationship existence isn't what you need and you need more fine grained conditions on the query. This can be applied using whereHas instead of has. This is allowing us to add conditions directly to the query that will be ran to determine the existence.

User::whereHas('comments', function ($q) {
    $q->where('created_at', '>=', \Carbon\Carbon::now()->subWeek());
})->get();

Here we are asking for all Users who have Comments that were created in the last week. Pretty powerful and handy stuff right there!

Like has we can also use a count of these records to determine this.

User::whereHas('comments', function ($q) {
    ...
}, '>=', 10)->get();

Now we are asking for all users who have at least 10 comments created in the last week.

Like has there is an inverse of the whereHas method: whereDoesntHave.

Nested Relationships

There is something that the eager loading and querying existence have in common, they can both use nested relationships.

User::with('comments.post')->get();
// load the comments relationship for each user
//    load the post relationship for each comment

User::whereHas('comments.post', function ($q) {
    $q->where('views', '>', 100);
})->get();
// add the condition on the post relationship of comments

Laravel Docs - Eloquent - Relationships - Eager Loading - Nested Eager Loading

Know your queries

You really should be checking your query log when dealing with this stuff. It will make you aware of the queries that Eloquent is deciding to do to actually accomplish all of this and let you know how many queries are being ran. A big problem people run into with these different methods is not having any idea of what queries are involved. If you don't you may not know where and what to add constraints onto.

Also Query Builder and Eloquent are not replacements for knowing SQL, they are tools to help you with SQL. You still need to know how SQL works so you can debug and figure out what is going on with your queries.

For reference of a way to enable and see the query log:

asklagbox - Laravel Tips and Tricks

Laravel Docs - Database: Getting Started - Running Raw SQL Queries - Listening for Query Events

Conclusion

Eloquent Relationships are very powerful. You can do a lot with them and this article was only showing how to do 2 things with them, though they are important to know. We can eager load and constrain and filter our result based on existence of some relationship criteria. These two things will cover a lot of what you will want to do when querying relationships.

For the other ways to interact with relationships check out my other article listed at the top of this article. It is slanted to point out some issues with awareness but has some good information in it about how the relationships work and what you can do to query them directly.

As always post any questions or comments you have below and I hope you enjoyed this quick article.