[FIXED] Find articles that have been tagged as X and Y but not as Z

Issue

Let's say I have Articles that can have many Tags. I'd like to be able to find all articles that have been tagged as X and Y, but not Z.

The database looks something like this:

articles

• id
• title
• body

tags

• id
• name

articles_tags

• article_id
• tag_id

Now, if I wanted to find all articles that have been tagged as both X and Y, I could do it like this:

$tags = ['X', 'Y'];
$articles = TableRegistry::get('Articles');
$articles = $articles
    ->find()
    ->innerJoinWith('Tags')
    ->where(['Tags.name IN' => $tags])
    ->group(['Articles.id'])
    ->having([$this->query()->newExpr('COUNT(DISTINCT Tags.name) = ' . count($tags))]);

Or if I wanted to find all articles that have not been tagged as Z, I could do it like this:

// ...
$articles = $articles
    ->find()
    ->notMatching('Tags', function ($q) use ($tags) {
        return $q->where(['Tags.name IN' => $tags]);
    });

But what if I want to find all articles that have been tagged as X and Y, but not Z? I have spent hours trying to get a working query, but I just can't figure it out.

If I combine these two queries, I get the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Tags.name' in 'where clause'

The combined query looks like this:

$tags = ['X', 'Y'];
$tags_not = ['Z'];
$articles = TableRegistry::get('Articles');
$articles = $articles
    ->find()
    ->innerJoinWith('Tags')
    ->where(['Tags.name IN' => $tags])
    ->group(['Articles.id'])
    ->having([$this->query()->newExpr('COUNT(DISTINCT Tags.name) = ' . count($tags))])
    ->notMatching('Tags', function ($q) use ($tags_not) {
        return $q->where(['Tags.name IN' => $tags_not]);
    });

Any ideas?

Solution

$excluded = $this->Articles
    ->find()
    ->select('id')
    ->matching('Tags', function($q) use($tags_not) {
        return $q->where(['Tags.name IN' => $tags_not]);
})->toArray();
$excludedIds = array_column($excluded, 'id');

$articles = $this->Articles
    ->find()
    ->where(['Articles.id NOT IN' => $excludedIds])
    ->matching('Tags', function($q) use($tags) {
        return $q->where(['Tags.name IN' => $tags]);
});

Answered By - danny3b