Laravel eloquent get all records wherehas all ids

问题:

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.

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

回答2:

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

回答3:

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