Logical Column in MySQL - How?

Question

I have a datamodel, let's say: invoices (m:n) invoice_items and currently I store the invoice total, calculated in PHP by totalling invoice_items, in a column in invoices. I don't like storing derived data as it paves the way for errors later.

How can I create a logical column in the invoices table in MySql? Is this something I would be better handling in the PHP (in this case CakePHP)?

Answer 1

Either you can return the derived one when you want it via

SELECT COUNT(1) as total FROM invoice_items

Or if invoices can be multiple,

//assuming that invoice_items.num is how many there are per row
SELECT SUM(num) as total FROM invoice_items

Or you can use a VIEW, if you have a certain way you want it represented all the time.

Answer 2

http://forge.mysql.com/wiki/MySQL_virtual_columns_preview

It's not implemented yet, but it should be implemented in mysql 6.0

Currently you could create a view.

Answer 3

Leo,

One thing you could do is to modify the afterFind() method in your model. This would recalculate the total any time you retrieve an invoice (costing runtime processing), but would mean you're not storing it in the invoices table, which is apparently what you want to avoid (correct if I'm wrong).

Try this:

class Invoice extends AppModel {
// .. other stuff
 function afterFind() {
  parent::afterFind();
  $total = 0;
  foreach( $this->data['Invoice']['InvoiceItems'] as $item )
   $total += ($item['cost'] * $item['quantity']);

  $this->data['Invoice']['total'] = $total;
 }
}

I may have messed up the arrays on the hasMany relationship (the foreach line), but I hope you get the jist of it. HTH,

Travis

Answer 4

There's something called Virtual Fields in CakePHP which allows you to achieve the same result from within your Model instead of relying on support from MySQL. Virtual Fields allow you to "mashup" various data within your model and provide that as an additional column in your record. It's cleaner than the other approaches here...(no afterFind() hacking).

Read more here: http://book.cakephp.org/view/1608/Virtual-fields