Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

creating virtual fields on the fly in CakePHP

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.

like image 770
Sankalp Avatar asked Dec 09 '22 12:12

Sankalp


1 Answers

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')
);
like image 191
arilia Avatar answered Jan 28 '23 11:01

arilia