php - CakePHP 3 包含选择字段

Question

我有以下数据库表:items、users、groups、itemImages。(items, groups) 和 (users, groups) 之间存在多对多关系，(items, itemImages) 之间存在一对多关系。所有适当的外键都已在 CakePHP 中设置为关联。

我如何使用 contain() 构建一个查询，该查询选择所有项目及其主图像，这些项目及其主图像位于一个组中，该组已作为主组分配给具有特定 ID 的用户？

为了更清楚，这是一个普通的 SQL 查询的样子:

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 代码:

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

Answer 1

ma​​tching() 方法可以帮助解决这个问题

$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 属性中。

关于 distict() 语句的注意事项:

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