Need some help with the conditions in CakePHP!

Question

I have three models linked in this manner: Item->Order->Payment

Order hasMany Item
Order hasOne Payment

Now, I am paginating Items and want to add a condition in it to find only items of that order which has payment of a particular id. I hope that makes sense :P

I added the condition as:

array('Payment.id'=>$id)

but it doesn't work. Obviously cause Payment is not associated with Item.

So, how can I go about this?

Answer 1

Why don't you add a condition:

array('Order.payment_id'=>$id)

I think this should work.

Answer 2

I am new to cakephp, maybe I am completily wrong but as I understand it you can use other models in your controller with the $uses variable. First make a query on payment model to get your order id, than you can use this id to find the corresponding items.

$uses=array('Item','Order','Payment');
$order_id=$this->Payment->find('first',array('fields'=>'order_id','conditions'=>array('id'=>$payment_id)));
$items=$this->Item->find('all',array('conditions'=>array('order_id'=>$order_id)));

I hope it help.

Answer 3

If you specify that you want two levels of recursion this should work. Im assuming you have

in Payment.php

//recursion level 1
var $belongsTo = array('Order');

in Order.php

//recursion level 2
var $hasMany = array('Items')

You are right that for paginate to work you must query the model you wish to page and sort the lists by.

in PaymentController.php

//Query two levels deep, so the $payment['Order']['Item'][0-n] will be present
var $paginate = array('recursive' => 2);

Note this method does generate another query for each row to retrieve items.

Answer 4

Make sure the debug level in app/config/core.php is set to 2 to see the database calls.

1) You can use Containable behaviour, in which case you need to put this in your Item model:

var $actsAs = array('Containable');

and this into your Items controller:

$items = $this->Item->find('all'
   , array (
      'contain' => array('Order' => array('Payment'))
      , 'conditions' => array('Payment.id' => $paymentId)
   )
)

However I suspect that that will do a left join onto the Payments table (as its a hasMany relationship). So you won't filter Items in any way.

2) If you can't get contains to work then I often use explict joins (read this bakery article by nate on joins) in my find queries. So in your Items controller you'd have:

$items = $this->Item->find('all'
   , array (
      , 'joins' => array(
         array(
            'table' => 'payments'
            , 'alias' => 'Payment'
            , 'type' => 'INNER'
            , 'conditions' => array(
                'Option.id = Payment.option_id'
            )
         )
      )
      , 'conditions' => array('Payment.id' => $paymentId)
   )
)

You may also need to specify the join onto the options table.