我有以下数据库表：项目,用户,组,item
Images.
(项目,组)和(用户,组)之间存在多对多关系,(项目,项目图像)之间存在一对多关系.
所有相应的外键都已在Cake PHP中设置为关联.
(项目,组)和(用户,组)之间存在多对多关系,(项目,项目图像)之间存在一对多关系.
所有相应的外键都已在Cake PHP中设置为关联.
如何使用contains()构造一个查询,该查询选择所有项目及其主图像,这些项目位于已被指定为具有特定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
$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()方法可以帮助解决这个问题
$items = $this->Items->find()
->hydrate(false)
->select(['Items.id','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]);
}
]);
我认为,在这种情况下,可以省略最后一个contains语句,因为所需的结果已经在_matchingData属性中.
关于distict()stament的注意事项：
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
