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

Question

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.

score 4 · Answer 1 · answered Apr 13 '16 at 09:29

4

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();

answered Apr 13 '16 at 09:29

Joel Hinz

24,719
6
62
75

1

thanks @Joel it worked for me I will give an up vote but as I am looping over it I will wait for a better answer. thanks man. – ARIF MAHMUD RANA Apr 13 '16 at 10:37

score 4 · Answer 2 · edited May 23 '17 at 10:29

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();

score 0 · Answer 3 · edited May 06 '22 at 17:13

0

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)
);

edited May 06 '22 at 17:13

miken32

42,008
16
111
154

answered May 06 '22 at 16:56

Ijaz Ahmed Bhatti

736
1
7
26

Adding some explanation of what these parameters do would make this answer more helpful. And what is `$list` or "genres"? Please tailor your answer to the question. – miken32 May 06 '22 at 17:11

score -1 · Answer 4 · answered Apr 13 '16 at 11:30

-1

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);

answered Apr 13 '16 at 11:30

alexleonard

1,314
3
21
37

Did you tried this @alexleonard? I have tried this earlier it didn't work. – ARIF MAHMUD RANA Apr 20 '16 at 06:20
1

This will never work because 'id' cannot be two values at the same time. Your subquery will return something like SELECT * FROM tags WHERE id = 1 and id = 2; – blisssan Oct 31 '19 at 11:49

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

4 Answers4

Linked