CakePHP virtual fields with two tables

Question

I have two tables in my webpage: Orders:

id int(11) NOT NULL AUTO_INCREMENT,    
created datetime NOT NULL,
payed datetime DEFAULT NULL,
PRIMARY KEY (`id`)

and Order_items:

id int(11) NOT NULL AUTO_INCREMENT,
orders_id int(11) NOT NULL,
amount int(11) NOT NULL,
price float NOT NULL,
PRIMARY KEY (`id`)

One Order can have many Order_items. The example records, Orders:

id, created, payed
14, '2013-05-14 22:40:49', NULL
15, '2013-05-14 22:41:03', NULL

Order_items:

id, orders_id, amount, price
12, 14, 1, 5
13, 15, 6, 5

I need to somehow link these tables using a Model. The main thing is that the Orders result-set should contain a virtual field which would show the Order sum of money: SUM(amount*price). I'm expecting the result of the Orders table:

id, created, payed, order_sum
14, '2013-05-14 22:40:49', NULL, 5
15, '2013-05-14 22:40:49', NULL, 30

Is it possible, or it is limited by the virtual fields limits? Any help or ideas would be appreciated :)

Answer 1

I can't believe it :) I have managed to solve this "problem".

Here is the solution: I have added another field into the order_items table - sum it has the value amount*price. And here is the Virtual field in the Order model:

        public $virtualFields = array(
            'amount_price'=>'
                SELECT SUM(sum) 
                FROM order_items
                where (orders_id = `Order`.`id`)
                GROUP by orders_id'

Hope it helps, happy baking :)

Answer 2

You could keep a 'total' field in your orders table, then use a callback method like afterSave() to update the amount. This way, you won't have to run a query to retrieve and calculate the totals EVERY time.