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

Question

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

Answer 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).