Consider 3 tables as below
class Post extends Model
{
public function tags()
{
return $this->belongsToMany(Tag::class, 'post_tags', 'post_id', 'tag_id');
}
}
posts table
|id | title |
+---+---------+
|1 | post1 |
|2 | post2 |
|3 | post3 |
|4 | post4 |
tags table
|id | value |
+---+---------+
|1 | tag01 |
|2 | tag02 |
|3 | tag03 |
|4 | tag04 |
post_tags table
| post_id | tag_id |
+----------+---------+
| 1 | 1 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 4 | 1 |
| 4 | 4 |
The only post that has both tag01 and tag04 is post with id = 4
But when I get posts with this code
Post::whereHas('tags', function($q) {
$q->whereIn('tag_id', [1, 4]);
}
I get all the posts that has tag01 or tag04.
I want to get Posts where have both tag01 and tag02 in its tags relation.
How can I achieve this result using Eloquent or if it's not possible using QueryBuilder
I think you can use multiple where condition
Post::whereHas('tags', function($q) {
$q->where('tag_id', 1);
})->whereHas('tags', function($q) {
$q->where('tag_id', 4);
})->get();
if ids are dynamic then
$id=[1,4];
Post::where(function($query)use($id){
foreach ($id as $value){
$query->whereHas('tags',function ($query)use($value){
$query->where('tag_id',$value);
});
}
})->get();
The whereHas method takes more arguments and one of them is a count:
Post::whereHas('tags', fn ($q) => $q->whereIn('tag_id', $tags), '=', count($tags))->get();
If you are looking for [1, 4] what this is saying is find me all Posts that have tags 1 or 4 but then only pick the ones that have exactly 2 of these (the count), which means find all Posts that have all these tags.
Laravel 8.x Docs - Eloquent - Relationships - Querying Relationship Existence whereHas