Rendering a calculated value that is not in the database

Question

I have a simple table with 3 values principle, interest, period. I want to show these 3 values in a table along with calculated simple interest. This interest is for display only, wont be stored in the DB. How can i achieve this ? I know I can use Javascript to compute the interest and render it, but can I do it via Cakephp?

Answer 1

You can do it in the SQL select statement, something like:

select Principle, Interest as [interest rate], Period, 
       principle * interest * period / 12 as [Interest Amount] 
    from your_table

When it gets back to PHP, this will look just like you'd selected from a table with four columns instead of three (i.e., the calculated value won't look any different from the others).

Answer 2

A few things you can do. One is to calculate it in the database. This is optimal if you can do it (IMO, I prefer to push as much data logic to the db as possible.) Of course, if you're doing complex calculations and database scalability is an issue you'll face soon, you can ease the load on your db by putting this in code.

Option 2 is to just calculate it before you display it. In the view, just write the PHP code to calculate what you need. This might be kinda hacky, depending on how frequently you need to display this (calculated) data.

Option 3 is to make that same calculation in the model. This is probably the most Cake-y way. Override your model's afterFind() method, and just run your calculations on the data retrieved. Add a new array index as needed. Then whenever any controller/view requests data from your model, said data will be returned with the calculated rows. Most likely this is how I'd implement it.

Answer 3

Check this link: Virtual fields

Basically you can define in your model virtual fields and you can use them as regular field in controller and view. You can use them in pagination too :)

Answer 4

In the view:

<?php echo $a * $b / $c ?>

OR

In the controller:

$calcVal = $a * $b / $c;
$this->set('calcVal');

then in the view:

<?php echo $calcVal ?>

Or any of the options listed above.

For simple stuff where I have the values in the view anyway, I'd go with the first one. On the other hand, for a complex calculation using values that I would otherwise not have a use for in the view, I'd go with the second.

Answer 5

http://book.cakephp.org/view/1048/Callback-Methods#afterFind-1050

class Account extends AppModel {

    public $name = "Account";

    public function afterFind( $results, $primary = false ){

        parent::afterFind( $results, $primary );
        if( $primary ){
            foreach( $results as $key => $value ){
                if( isset( $val[ $this->alias ] )){
                    // set the a new index for each row returned to the value of Account::__calculateSomething at the correct key
                    $results[ $key ][ $this->alias ][ 'calulated_field_name' ] = $this->__calculateSomething( $results[ $key ][ $this->alias ] );
                }
            }
        }
        return $results;
    }
}

The just do your aggregating or caluculations in the __calulateSomething( $data ) function in the Account model.

This is just an example of course, but it shows how to use the $primary parameter that gets passed to the afterFind callback ( meaning the afterFind is being triggered for find calls made directly to this model, not as part of an association. )

You might need to check the format of the array - I didn't do that here.

Answer 6

calculating from the database will be easier using the virtual fields

see this

http://book.cakephp.org/view/1588/virtualFields