mysql - 在 cakephp 3.0 中存在多重关联时，在查询生成器中使用条件吗？

Question

当我在 cakephp 3.0 中使用多个包含内的条件时，我在使用查询生成器的多级关联中遇到麻烦。假设我必须获取单个商店的数据，所以我尝试添加条件中的 storeId，但它不起作用，否则其余数据从所有商店获取正确，下面是我正在使用的查询生成器:-

// generate query               
$bestSellingReportData = $this->ArticleMaster->find('all')->contain([
    'Category' => [
        'fields' => ['Cat_Id', 'Cat_Code']
    ],
    'size_category' => [
        'fields' => ['sizeCat_Id', 'sizeCat_Code']
    ],                  
    'ItemMaster' => [
        'Invoicedetaile' => [
            'Invoice' => [
                'Store' => [
                    'fields' => ['Store_Id', 'Store_Code']
                ],
                'conditions' => ['Invoice.StoreId ='.$this->request->data['storeId']],
            ],
        ],
    ],
]);

$bestSellingReportData->select(['totalSoldItems' => $bestSellingReportData->func()->sum('Invoicedetaile.Qty')])
->matching('ItemMaster.Invoicedetaile', function ($q) {
    return $q->where([
        'AND' => [
            'Invoicedetaile.ItemId = ItemMaster.Item_ID',
        ]
    ]);
})
->group(['ArticleMaster.Article_Code'])
->order(['totalSoldItems' => 'DESC'])
->autoFields(true);

我尝试使用添加条件和 in where 子句以两种方式添加条件。但数据不会根据条件(即 storeId)进行过滤

Answer 1

在进行了多个小时的研发之后为我工作。下面是适合我的查询生成器。

   $bestSellingReportData = $this->ArticleMaster->find('all')->contain([
    'Category' => [
        'fields' => ['Cat_Id', 'Cat_Code']
    ],
    'size_category' => [
        'fields' => ['sizeCat_Id', 'sizeCat_Code']
    ],                  
    'ItemMaster' => [
        'Invoicedetaile' => [
            'Invoice',
        ],
    ],
]);

$storeId = $this->request->data['storeId'];
$bestSellingReportData->select(['totalSoldItems' => $bestSellingReportData->func()->sum('Invoicedetaile.Qty')])
->matching('ItemMaster.Invoicedetaile.Invoice', function ($q) use ($storeId) {
    return $q->where([
        'AND' => [
            'Invoicedetaile.ItemId = ItemMaster.Item_ID',
            'Invoice.StoreId ='.$storeId,
        ]
    ]);
})
->group(['ArticleMaster.Article_Code'])
->order(['totalSoldItems' => 'DESC'])
->autoFields(true);