CakePHP containable for join not searching additional table

Question

I'm trying to join two tables and I found this post on accomplishing it using containables.

The tables I'm trying to join are orders and order_items. Here's the code in my Order and Order_Item controller

Class OrderItem extends AppModel {
    public $useTable = 'order_items';
    public $actsAs = array('Containable');
    public $belongsTo = array('Order');
}

class Order extends AppModel {  
    public $actsAs = array('Containable');
    public $hasMany = array('OrderItem');
}

Here's the query in the OrdersController

$order_list = $this->Order->find('all', array(              
                                'contain' => array('OrderItem'),
                                'conditions' => array(
                                'Order.order_status' => 'filled',
                                'OrderItem.item_status' => 'filled'
                                )
                            ));

Right now it doesn't appear to be querying the order_items table. I get the following error

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Order_Item.item_status' in 'where clause'

Upon further inspection, the query isn't even searching the order_items table, just the orders table. I assume this is true because it didn't find the column I specified in the conditions.

Here's the resulting query

SQL Query: SELECT `Order`.`id`, `Order`.`userid`, `Order`.`order_status`, `Order`.`email`, 
`Order`.`total`, `Order`.`fullName`, `Order`.`address`, `Order`.`city`, `Order`.`state`, 
`Order`.`zip`, `Order`.`created`, `Order`.`modified` FROM `ordersdb`.`orders` AS `Order` 
 WHERE `Order`.`order_status` = 'filled' AND `OrderItem`.`item_status` = 'filled'

score -1 · Answer 1 · answered Jun 14 '13 at 04:13

Containable is doing separate query for each model that you pass into the "Contain" key. In your case, item_status field does not exist in Order table that's why you have that error.

I think you have to do something like this

 $order_list = $this->Order->find('all', array(              
                        'contain' => 'OrderItem.item_status == filled',                                                                 
                        'conditions' => array(
                        'Order.order_status' => 'filled'
                        )
                    ));

See the cookbook for more information http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html

CakePHP containable for join not searching additional table

1 Answers1