如何按条件过滤关联模型? [英] How to filter by conditions for associated models?

问题描述

我有一个关于用户和联系人的belongsToMany 关联.

I have a belongsToMany association on Users and Contacts.

我想找到给定用户的联系人.我需要类似的东西

I would like to find the Contacts of the given User. I would need something like

$this->Contacts->find()->contain(['Users' => ['Users.id' => 1]]);

食谱中提到了包含条件、自定义查找器方法和通过关联键唱歌，但我没有找到如何将这些组合在一起.

The cookbook speaks about giving conditions to contain, custom finder methods and sing through association key, but I did not find out how to put these together.

推荐答案

使用 Query::matching() 或 Query::innerJoinWith()

当从 Contacts 表中查询时，您要查找的是 Query::matching() 或 Query::innerJoinWith()>，不是(仅)Query::contain().

Use Query::matching() or Query::innerJoinWith()

When querying from the Contacts table, then what you are looking for is Query::matching() or Query::innerJoinWith(), not (only) Query::contain().

注意 innerJoinWith() 通常是首选，以避免出现严格分组的问题，因为 matching() 会将关联的字段添加到选择列表中，这可能会导致问题，因为它们通常在功能上不依赖.

Note that innerJoinWith() is usually preferred in order to avoid problems with strict grouping, as matching() will add the fields of the association to the select list, which can cause problems as they are usually not functionally dependent.

参见食谱>数据库访问ORM >查询生成器按关联数据过滤

以下是使用您的表格的示例:

Here's an example using your tables:

$this->Contacts
    ->find()
    ->innerJoinWith('Users', function(CakeORMQuery $q) {
        return $q->where(['Users.id' => 1]);
    });

这将自动将所需的联接 + 条件添加到生成的查询中，以便仅检索与至少一个 ID 为 1 的用户相关联的联系人.

This will automatically add the required joins + conditions to the generated query, so that only those contacts are being retrieved that are associated to at least one user with the id 1.

如果您通过 hasMany 和 belongsTo 手动设置多对多关联，您可以直接定位连接表:

In case you'd have a manually set up many to many association via hasMany and belongsTo, you can directly target the join table:

$this->Contacts
    ->find()
    ->innerJoinWith('ContactsUsers', function(CakeORMQuery $q) {
        return $q->where(['ContactsUsers.user_id' => 1]);
    });

包括收容

如果您真的希望在结果中也返回所有关联，那么也只需继续使用 contain():

$this->Contacts
    ->find()
    ->contain('Users')
    ->innerJoinWith('Users', function(CakeORMQuery $q) {
        return $q->where(['Users.id' => 1]);
    });

这将包含属于某个联系人的所有用户.

That would contain all users that belong to a contact.

如果您有多个匹配项，而您只想包含这些匹配项，则还必须过滤包含项.在这个例子中，它没有多大意义，因为只有一个匹配，但在其他情况下它可能有用，例如，如果你想匹配所有具有活跃用户的联系人，并检索仅包括的联系人活跃的关联用户:

In cases where you have multiple matches, and you'd wanted to contain only those matches, you'd have to filter the containment too. In this example it doesn't make much sense since there would be only one match, but in other situations it might be useful, say for example if you'd wanted to match all contacts that have active users, and retrieve the contacts including only the active associated users:

$this->Contacts
    ->find()
    ->contain(['Users' => function(CakeORMQuery $q) {
        return $q->where(['Users.active' => true]);
    }])
    ->innerJoinWith('Users', function(CakeORMQuery $q) {
        return $q->where(['Users.active' => true]);
    })
    ->group('Contacts.id');

鉴于可能存在重复项，即单个联系人有多个活动用户，您可能需要相应地对内容进行分组，以避免检索重复的联系人记录.

Given that there could be duplicates, ie multiple active users for a single contact, you'll probably want to group things accordingly, in order to avoid retrieving duplicate contact records.

您还可以通过使用 Query::contain() 中已知的点标记路径语法以这种方式定位更深层次的关联.举例来说，您有一个 Users hasOne Profiles 关联，并且您只想匹配那些想要接收通知的用户，可能如下所示:

You can also target deeper associations that way, by using the dot notated path syntax known from Query::contain(). Say for example you had a Users hasOne Profiles association, and you want to match only on those users that want to receive notifications, that could look something like this:

->innerJoinWith('Users.Profiles', function(CakeORMQuery $q) {
    return $q->where(['Profiles.receive_notifications' => true]);
})

这将自动创建所有必需的附加连接.

This will automatically create all the required additional joins.

通过这些关联和您的简单需求，您还可以轻松地从另一端进行查询，即通过 Users 表并仅使用 Query::contain()包括关联的联系人，如

With these associations and your simple requirements, you could also easily query from the other side, ie via the Users table and use just Query::contain() to include the associated contacts, like

$this->Users
    ->find()
    ->contain('Contacts')
    ->where([
        'Users.id' => 1
    ])
    ->first();

然后可以在实体 contacts 属性中找到所有联系人.

All the contacts can then be found in the entities contacts property.

这篇关于如何按条件过滤关联模型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！