如何匹配与一组特定的其他记录关联的记录? [英] 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']);
});

但是现在我想查找同时具有两个标签的所有用户. 我尝试使用->andWhere而不是->orWhere，但是结果始终为空.

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?

谢谢

推荐答案

有几种方法可以实现此目的，一种是将结果分组并使用HAVING比较不同标签的数量

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

这将仅选择具有两个不同标签的那些用户，它们只能是Tag1和Tag2，因为这些是唯一被加入的用户.如果name列是唯一的，则您可以而是依靠主键.

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.

IN顺便说一句.基本上与您的OR条件相同(数据库系统将相应地将IN扩展为OR条件).

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

这篇关于如何匹配与一组特定的其他记录关联的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！