Use of condition in query builder in case of multiple association in cakephp 3.0?

Question

I am facing trouble in multilevel association using query builder when i used conditions inside multiple contain in cakephp 3.0. Lets suppose i have to get a data of single store so i am trying to add the storeId in condition but its not working else rest of data is fetching correct from all store below are the query builder that i am using:-

// 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);

I try to add the condition in both way using add condition and in where clause. But data is not filtering based on conditions i.e storeId

"_doesn't work_" is not a proper problem description. Even if the problem might be obvious for people that know the CakePHP internals, please always be as specific as possible as to what _exactly_ happens, and what _exactly_ you'd expect to happen instead (ie what data do you receive, what data do you need instead, what query is being generated, and how should it look like instead). Show the data that you are working with (ex the schema and the association setup), your debugging attempts, and possible errors. — ndm, Jun 14 '17 at 14:45
There is not any error i am trying to generate the best selling reports from the few tables i.e ArticleMaster, ItemMaster, Invoice, Invoice Detail, right now its fetching all data and i have to filter data based on storeId (shop) Below condition that i am passing within query 'conditions' => ['Invoice.StoreId ='.$this->request->data['storeId']], then data should filter based on this condition only this condition is not working for me that is the problem. — kantsverma, Jun 15 '17 at 05:01
That's still not overly clear, just as a hint for future questions, looking at your answer, you want to filter `ArticleMaster` by associated `Invoice`s for a specific store. From your example that would have been hard to tell, as it looks like that you'd possibly want to filter the containments instead. — ndm, Jun 15 '17 at 10:48

score 0 · Answer 1 · answered Jun 15 '17 at 09:21

0

Work for me after doing R&D of many hours. Below is the query builder that work for me.

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

answered Jun 15 '17 at 09:21

kantsverma

606
1
11
31

That's an SQL injection vulnerability however! Never insert (user) data into snippets directly, always either use the `key => value` syntax, or bindings! `'Invoice.StoreId' => $storeId`: **https://book.cakephp.org/3.0/en/orm/query-builder.html#sql-injection-prevention** – ndm Jun 15 '17 at 10:48
@ndm ok i understand that i should use it like 'Invoice.StoreId' => $storeId can you please let me know how should i use the conditions i.e. 'Invoicedetaile.ItemId = ItemMaster.Item_ID', ? – kantsverma Jun 15 '17 at 19:35
There's no external/user data in that value, so it's safe. It can be rewritten using expressions in case required: **https://stackoverflow.com/questions/43725445/how-to-compare-two-fields-columns-in-a-condition/43726213#43726213** – ndm Jun 15 '17 at 21:47

Use of condition in query builder in case of multiple association in cakephp 3.0?

1 Answers1