'Laravel eloquent get all records wherehas all ids in many to many relation

I have a Posts table it has three fields id, title, description.

My Post Model

class Post extends Model
{
    use SoftDeletes;

    protected $fillable = ['title', 'description'];

    public function tags()
    {
        return $this->belongsToMany(Tag::class, 'post_tag');
    }
}

My Tag Model

class Tag extends Model
{
    use SoftDeletes;

    protected $fillable = ['name'];

    public function posts()
    {
        return $this->belongsToMany(Post::class, 'post_tag');
    }
}

Now I want to get posts & paginate where I have a tag filter e.g I have two tags animals & news which has id 1 & 2. Now I want to get all posts which has tag 1 & 2 & paginate. Here is what I tried

        Post:: with('tags')->whereHas('tags', function($q) {
            $q->whereIn('id', [1, 2]);
        })->paginate();

But here as I am whereIn it returns posts has tags 1 or 2 or both. But I want post who has both tag id 1 & 2.

I am using Laravel 5.2.

phplaraveleloquentrelationship


Solution 1:[1]

You'll have to loop through your list of ids to add that condition, then. For instance:

$query =  Post::with('tags');
foreach ($ids as $id) {
    $query->whereHas('tags', function($q) use ($id) {
        $q->where('id', $id);
    });
}
$query->paginate();

Solution 2:[2]

I have been looking for the same thing and inspired by this stackoverflow MySQL answer, I have ended up with this

Code:

Post:: with('tags')->whereHas('tags', function($q) {
    $idList = [1,2];
    $q->whereIn('id', $idList)
      ->havingRaw('COUNT(id) = ?', [count($idList)])
})->paginate();

Because I think I might use it in a few places I have made it into a trait which you can view here. Which if you included the trait in your Post class you could use like the following.

Code:

Post::with('tags')->whereHasRelationIds('tags', [1,2])->paginate();

Solution 3:[3]

You can also use whereHas()'s third and fourth parameter in combination with whereIn():

$keys = [1, 2];

$list->whereHas(
    'genres',
    fn ($query) => $query->whereIn('id', $keys),
    '=',
    count($keys)
);

Solution 4:[4]

I don't think there's a built in method for this, but I would suggest putting the foreach loop inside the whereHas method just for neatness sake.

$query = Post::with('tags')->wherehas('tags', function ($q) use ($ids) {
    foreach ($ids as $id) {
        $q->where('id', $id);
    }
})->paginate(10);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Joel Hinz
Solution 2 Community
Solution 3 miken32
Solution 4 alexleonard

Related Questions