CakePHP join tables in find query

Question

I have Workposition model. It is linked in database with Orders with belongsTo relationship. So, I need to find specific workpositions bz the conditions related to Orders model. So, When I use for example suck kind of find:

$workpositions = $this->Workposition->find('all', array(
                'conditions' => array(
                        'Order.type' => 'N'
                )
));

CakePHP understand the Order.id notation. But when i'm trying to use joins tables:

$workpositions = $this->Workposition->find('all', array(
                'conditions' => array(
                        'Order.type' => 'N'
                )
                'joins' => array(
                        array('table' => 'ordergroups_orders',
                            'alias' => 'OrdergroupsOrder',
                            'type' => 'INNER',
                            'conditions' => array(
                                    'Order.id = OrdergroupsOrder.order_id',
                                    'OrdergroupsOrder.ordergroup_id' => '3',                                    
                            )
                    )               
        )));

It gives me an error : Column not found: 1054 Unknown column 'Order.id' in 'on clause'. So it doesn't understand Order.id notation. What can be the problem ?

I tried also to make something like this :

$workpositions = $this->Workposition->find('all', array(
                'conditions' => $conditions,
                'joins' => array(
                    array('table' => 'orders',
                        'alias' => 'Orders',
                        'type' => 'INNER',
                    ),
                    array('table' => 'ordergroups_orders',
                            'alias' => 'OrdergroupsOrder',
                            'type' => 'INNER',
                            'conditions' => array(
                                    'Orders.id = OrdergroupsOrder.order_id',
                                    'OrdergroupsOrder.ordergroup_id' => $ordergroup_ids,                                    
                            )
                    )   
        )));

But i get error Column not found: 1054 Unknown column 'Array' in 'on clause'(Array to string conversion). So it doesn't undestand my array of ids, while it undestands it without the binding of Order model, when the find Method sees Order.

Willian Willian · Accepted Answer · 2016-11-10T17:28:46

The join conditions must be the array value, not key => value.

Try change the line

'OrdergroupsOrder.ordergroup_id' => $ordergroup_ids,

to

'OrdergroupsOrder.ordergroup_id = $ordergroup_ids',

$ordergroup_ids is an Array? Try to use a single id.

CakePHP join tables in find query

1 Answers