Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
717 views
in Technique[技术] by (71.8m points)

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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')
);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...