CakePHP 3包含​​选择字段 [英] CakePHP 3 contain select fields

问题描述

我有以下数据库表：项目，用户，组，itemImages。
（项目，组）和（用户，组）之间存在多对多关系，一对多关系（项目，itemImages）之间有许多关系。
所有合适的外键都已在CakePHP中作为关联设置。

I have the following DB tables: items, users, groups, itemImages. There are many to many relations between (items, groups) and (users, groups), and one to many between (items, itemImages). All the appropriate foreign keys have been setup in CakePHP as associations.

如何使用contains（）构造一个查询，它选择所有项目及其主图，它们在一个已经被分配为具有特定id的用户的主组的组中？

How can I construct a query with contain() which selects all items and their main image, which are in a group which has been assigned as a main group to a user with a particular id?

为了使它更清楚，这里是一个普通的SQL查询看起来像：

To make it clearer, here is what a plain SQL query would look like:

SELECT items.id AS itemId, items.name, itemImages.url AS itemUrl
FROM items
INNER JOIN groups_items ON groups_items.item_id = items.id
INNER JOIN groups ON groups_images.group_id = groups.id
INNER JOIN groups_users ON groups_users.group_id = groups.id
INNER JOIN users ON groups_users.user_id = users.id
INNER JOIN itemImages ON itemImages.item_id = items.id
WHERE groups_users.isMainGroup = 1
    AND users.id = :userId
    AND itemImages.isMainImage = 1

CakePHP代码我现在有：

CakePHP code I have for now:

$items = $this->Items->find()
            ->hydrate(false)
            ->contain([
                'Groups.Users' => function($q) use ($userId){
                    return $q->where(['isMainGroup' => 1, 'Users.id' => $userId]);
                },
                'ItemImages' => function($q){
                    return $q->where(['isMainImage' => 1]);
                },
            ]);
            //->select(['itemId' => 'Items.id', 'Items.name', 'itemUrl' => 'itemImages.url']);

推荐答案

匹配可帮助此

matching() method can help with this

$items = $this->Items->find()
        ->hydrate(false)
        ->select(['Items.id', 'Items.name', 'itemImages.url'])
        ->distinct(['Items.id'])
        ->matching('Groups.Users', function ($q) use ($userId) {
            return $q->where(['Groups.isMainGroup' => 1, 'Users.id' => $userId]);
        })
        ->matching('ItemImages', function ($q) {
            return $q->where(['ItemImages.isMainImage' => 1]);
        })
        ->contain([
            'ItemImages' => function ($q) {
                return $q->autoFields(false)
                         ->select(['id','url'])
                         ->where(['ItemImages.isMainImage' => 1]);
            }
        ]);

我认为，在这种情况下，最后一个包含语句可以被忽略，因为期望的结果已经在 _matchingData 属性。

I think, in this case, the last contain statement can be ommited, because desired result is already in _matchingData property.

请注意 distict（） stament：

Note on distict() stament:

由于这个函数将创建一个INNER JOIN，你可能会考虑
调用distinct查询，因为你可能会得到重复的行，如果
你的条件don' t过滤它们

As this function will create an INNER JOIN, you might want to consider calling distinct on the find query as you might get duplicate rows if your conditions don’t filter them already

http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data

这篇关于CakePHP 3包含​​选择字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！