Hello friends.
Continue with Relationships in Laravel, this article I will share about the query while using relationships (Querying Relations). Well, the link for anyone who wants to find part 2 here.
3. Querying Relations
As you know in the previous section, after we define the relationships in the model, we can call it as a property of the model to retrieve data. However, we can also call it just to return an instance of that relationship without actually needing to return data. Moreover, all types of Eloquent relationships act as query builders , so after calling the relationship, you can continue writing more queries and adding constraints before making the query with the database.
For example, a User model has a 1 – n relationship with the Post model (1 user has many posts).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?php namespace AppModels; use IlluminateDatabaseEloquentModel; class User extends Model { /** * Lấy tất cả các post của user */ public function posts() { return $this->hasMany(Post::class); } } |
To get all user posts with $ id = 1, I will use this:
1 2 3 4 | $user = AppUser::find(1); $posts = $user->posts; |
So what if I only want to get posts with the active = 1 attribute? Instead of checking the results above, we can write more queries after the relationship. As simple as this:
1 2 | $posts = $user->posts()->where('active', 1)->get(); |
Note : I call posts() , not posts .
Similarly, you can use other query builder queries, just read the document and remember to use it 
orWhere When appending the query after the relationship, we should pay attention to orWhere . The example is easy to understand. To get posts with active = 1 or votes >= 100 , will write like this
1 2 3 4 5 | $user->posts() ->where('active', 1) ->orWhere('votes', '>=', 100) ->get(); |
This code corresponds to the query: select * from posts where user_id = ? and active = 1 or votes >= 100 ==> Wrong compared to what we expected. So, when using orWhere to pay attention a little, need to group the conditions to query properly.
1 2 3 4 5 6 7 | $user->posts() ->where(function (Builder $query) { return $query->where('active', 1) ->orWhere('votes', '>=', 100); }) ->get(); |
Equivalent: select * from posts where user_id = ? and (active = 1 or votes >= 100) .
3.1. Dynamic Properties
As mentioned earlier, when a relationship is defined, it can be called as a Dynamic Properties (dynamic property) of the model. Dynamic Properties is “lazy loading” (I think it should not be translated), meaning relationships only load when you call it (eg $ user-> posts).
In the following, I will talk about Eager loading, this is a way for us to preload relationships as soon as querying data, greatly reducing the number of queries with relations. (Remember to read the next section).
3.2. Query existence relationship
The problem arises when you want to query based on the existence of a relationship of an object. For example, you want to retrieve posts that have at least 1 comment. How would you do it ???
Very simple, you will use the has and orHas .
1 2 | $posts = Post::has('comments')->get(); |
or a little more binding
1 2 | $posts = Post::has('comments', '>=', 3)->get(); |
If we want to query against nested relations, we will use the sign . Please. For example, querying posts with at least 1 comment with 1 vote:
1 2 | $posts = Post::has('comments.votes')->get(); |
If you need stronger constraints then use whereHas and orWhereHas . These methods allow us to further customize the relationship constraint. For example, taking posts with comments with content that meet conditions:
1 2 3 4 5 6 7 | use IlluminateDatabaseEloquentBuilder; // lấy ra các post có ít nhất 10 comment có chứa từ foo $posts = Post::whereHas('comments', function (Builder $query) { $query->where('content', 'like', 'foo%'); }, '>=', 10)->get(); |
3.3. Queries are based on a nonexistent relationship
Contrary to section 3.2, here we will retrieve posts without any comment . We use doesntHave and orDoesntHave .
1 2 | $posts = Post::doesntHave('comments')->get(); |
Similar to has in section 3.2, to be able to query with stronger constraints, we can use whereDoesntHave and orWhereDoesntHave . For example:
1 2 3 4 5 6 7 | use IlluminateDatabaseEloquentBuilder; // lấy ra các bài post không có comment có nội dung chứa từ foo $posts = Post::whereDoesntHave('comments', function (Builder $query) { $query->where('content', 'like', 'foo%'); })->get(); |
3.4. Querying with Polymorphic Relationships
To query with Polymorphic Relationships, we use the methods whereHasMorph , whereDoesntHaveMorph and the corresponding methods. Pay attention to the parameters passed in these functions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | use IlluminateDatabaseEloquentBuilder; // Lấy ra các comment của post hoặc video có title chứa từ foo $comments = Comment::whereHasMorph( 'commentable', [Post::class, Video::class], function (Builder $query) { $query->where('title', 'like', 'foo%'); } )->get(); // Lấy ra các comment của post có title không chứa từ foo $comments = AppComment::whereDoesntHaveMorph( 'commentable', Post::class, function (Builder $query) { $query->where('title', 'like', 'foo%'); } )->get(); |
You can also use the $type parameter to add conditions to the relationship.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | use IlluminateDatabaseEloquentBuilder; $comments = Comment::whereHasMorph( 'commentable', [Post::class, Video::class], function (Builder $query, $type) { $query->where('title', 'like', 'foo%'); if ($type === Post::class) { $query->orWhere('content', 'like', 'foo%'); } } )->get(); |
To be able to query from all model types, instead of passing an array of models to the second parameter of the method, we can replace it with the * symbol.
3.4. Counting results returned from relationship (Counting Related Models)
If you need to count the number of results returned from a relationship and don’t need to load them, you can use the withCount method. This function will add a column {relation}_count into the result, then you can call it as a Dynamic Properties
1 2 3 4 5 6 | $posts = Post::withCount('comments')->get(); foreach ($posts as $post) { echo $post->comments_count; } |
- Raising a bit, we can count many relationships, combined with additional constraints. For example:
1 2 3 4 | $posts = Post::withCount(['votes', 'comments' => function (Builder $query) { $query->where('content', 'like', 'foo%'); }])->get(); |
Then get the results by:
1 2 3 | echo $posts[0]->votes_count; echo $posts[0]->comments_count; |
- Count 1 relation many times with different conditions. For example:
1 2 3 4 5 6 7 8 9 10 11 | $posts = Post::withCount([ 'comments', 'comments as pending_comments_count' => function (Builder $query) { $query->where('approved', false); }, ])->get(); echo $posts[0]->comments_count; echo $posts[0]->pending_comments_count; |
- If written with the
selectmethod, we will writewithCountafter theselect - The
loadCountmethod helps us load the number of relationship results after the parent model has been accessed.
1 2 3 4 | $book = Book::first(); $book->loadCount('genres'); |
To add a constraint to this function, we pass in an array with the key as the relative name, the value is the Closure executes the query.
1 2 3 4 | $book->loadCount(['reviews' => function ($query) { $query->where('rating', 5); }]) |
summary
In Part 3, I have shared how to improve queries when using relationships in Laravel. The next part I will share about Eager Loading, welcome to read.