Find by conditions on associated model in CakePHP 3

Question

I have two tables orders and sub_orders. Their association is

$orders->hasMany('SubOrders', [
   'foreignKey' => 'order_id'
]);

Both tables have invoice_no and sub_invoice columns in orders and sub_orders respectively.

I have to find records from orders table containing associated sub_orders where $trackingId will match either Orders.invoice_no or SubOrders.sub_invoice

$findOrder = $this->Orders->find('all', [
    'conditions' => [
      'OR' => [
         'Orders.invoice_no' => $trackingId,
         'SubOrders.sub_invoice' => $trackingId
       ]
     ],
     'contain' => [
        'SubOrders'
     ]
  ]);

But this gives error

Column not found: 1054 Unknown column 'SubOrders.sub_invoice' in 'where clause'

`$trackingId` will match either from `Orders` or from `SubOrders`. can't use `and` because condition for both will be never satisfied — Anuj TBE, Sep 18 '16 at 14:40

Derek · Accepted Answer · 2018-07-21T22:21:53.330

5

Try doing the query like this:

$findOrder = $this->Orders->find()
->where(['Orders.invoice_no' => $trackingId])
->contain(['SubOrders' => function ($q) use ($trackingId) {
   return $q

        ->where(['SubOrders.sub_invoice' => $trackingId]);
}
]);

edited Jul 21 '18 at 22:21

answered Sep 18 '16 at 15:24

Derek

850
9
19

Getting undefined variable "$trackingId", for line "where(['SubOrders.sub_invoice' => $trackingId]);" – bikash.bilz Jul 20 '18 at 13:51
bikash.bitz - That's because this example was using a variable they were setting already. – Derek Jul 21 '18 at 00:08
I tried your solution, At line number 2(in where condition) $trackingId is defined but In line number 5 getting error $trackingId is not defined. – bikash.bilz Jul 21 '18 at 04:58
1

change it from **function ($q)** to **function ($q) use ($trackingId)** – Derek Jul 21 '18 at 22:20
How to achieve the same for other associations. Let's consider I have a model Packages which is associated with orders. I want to search all the 3 table for the existing of the $trackingId (Condition for all 3 models are in OR). – bikash.bilz Jul 23 '18 at 06:51
Here is my questions link if you like, https://stackoverflow.com/questions/51473939/use-or-conditions-on-associated-model-in-cakephp-3 – bikash.bilz Jul 23 '18 at 07:52

Find by conditions on associated model in CakePHP 3

1 Answers1

Linked