CakePHP 3包含​​选择字段

前端之家收集整理的这篇文章主要介绍了CakePHP 3包含​​选择字段前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有以下数据库表:项目,用户,组,item Images.
(项目,组)和(用户,组)之间存在多对多关系,(项目,项目图像)之间存在一对多关系.
所有相应的外键都已在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

我现在拥有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']);
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

原文链接:https://www.f2er.com/php/135134.html

猜你在找的PHP相关文章