How to match records that are associated with a specific set of other records?

I am trying to add to two different search variations to my project.
There is a model “User” and a model “Tag”. A User has many Tags.
Now I want to be able to search the Users with specific Tags.
Either I want to show all Users that has any of the specified tags.
I got this working this way:

$query = $this->Users->find();
$query->matching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'Tag1'])
             ->orWhere(['Tags.name' => 'Tag2']);
});

But now I want to find all Users that have both Tags at the same time.
I tried ->andWhere instead of ->orWhere, but the result is always empty.

How can I find Users that contain multiple Tags?

Thanks

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

There are a few ways to achieve this, one would be to group the results and use HAVING to compare the count of the distinct tags

$query = $this->Users
    ->find()
    ->matching('Tags', function ($query) {
        return $query->where(['Tags.name IN' => ['Tag1', 'Tag2']]);
    })
    ->group('Users.id')
    ->having([
        $this->Users->query()->newExpr('COUNT(DISTINCT Tags.name) = 2')
    ]);

This will select only those users that have two distinct tags, which can only be Tag1 and Tag2 since these are the only ones that are being joined in. In case the name column is unique, you may count on the primary key instead.

The IN btw. is essentially the same as your OR conditions (the database system will expand IN to OR conditions accordingly).

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0