creating virtual fields on the fly in CakePHP

Question

I wish to create virtual fields on the fly.

My Order & Order Details are like...

//Order Model
class Order extends AppModel {
    public $name = 'Order';
    public $actsAs = array('Containable');
    public $hasMany = array(
        'OrderDetail' => array(
            'className' => 'OrderDetail',
            'foreignKey' => 'order_id',
            'dependent' => true
        ),
    );
}

//OrderDetail Model
  class OrderDetail extends AppModel {
    public $name = 'OrderDetail';
    public $actsAs = array('Containable');
    public $belongsTo = array(
        'Order' => array(
            'className' => 'Order',
            'foreignKey' => 'order_id',
            'dependent' => true
        ),
    );
}

The model relation is like this. I am creating virtual fields on the fly so that I can count number of items and their cost in order pagination. I know the weird way of requestAction. Is their any way other I can perform my task.

My tried code is

        $this->Order->virtualFields['totalCost'] = 0;
        $this->Order->virtualFields['totalItem'] = 0;
        $fields = array('Order.id', 'Order.order_key', 'Order.delivery_date','COUNT(`OrderDetail`.`order_id`) AS `Order__totalItem`', 'SUM(`OrderDetail`.`cost`) AS `Order__totalCost`');         

        $this->paginate['Order'] = array("fields"=>$fields, 'conditions' => array("AND" => array($condition, "Order.status" => 3)), 'limit' => '50', 'order' => array('Order.id' => 'DESC'));

It ends me with the mysql not found column Unknown column 'OrderDetail.order_id' in 'field list'. I haven't put recursive or bind my model to find something else. Why this error is generated ? How can I achieve my aim.

Answer 1

you can create virtual fields on the fly. Your syntax is correct but you can do also this way:

$this->Order->virtualFields['totalCost'] = 'SUM(`OrderDetail`.`cost`)';
$this->Order->virtualFields['totalItem'] = 'COUNT(`OrderDetail`.`order_id`)';

the problem here is that Order is in HasMany relationship with OrderDetail. CakePHP in this case doesn't join the tables but rather makes two queries, one for the Orders and one for the OrderDetail and then merge the recordsets.

what can you do?

you can paginate OrderDetail instead of Order, and group it by Order.id

$fields = array
(
    'Order.id', 
    'Order.order_key', 
    'Order.delivery_date', 
    'Order.totalItem', 
    'Order.totalCost'
);         

$this->paginate['OrderDetail'] = array
(
    "fields"=> $fields, 
    'conditions' => array("AND" => array($condition, "Order.status" => 3)), 
    'limit' => '50', 
    'order' => array('Order.id' => 'DESC'),
    'group' => array('Order.id')
);