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