CakePHP virtual field that is a sum of three other virtual fields?

Question

I have a virtual field in CakePHP that needs to be a SUM of three very disparate SQL queries in my User model. I'm trying to accomplish this by having a virtual field that is a sum of 3 other virtual fields.

var $virtualFields = array (
        'field_one' => 'select coalesce(sum(coalesce(t_a.field, 0)), 0)*10 as field_one from t_a join t_b on t_a.t_b_id = t_b.id where t_b.user_id=User.id',
        'field_two' =>  'select coalesce(sum(coalesce(t_c.field, 0)), 0)*2 as field_two from t_d left join (t_c) on (t_d.id=t_c.t_d_id) where t_d.user_id = User.id',
        'field_three' => 'select coalesce(sum(coalesce(value, 0)), 0) as field_three from t_e where user_id=User.id',
        'field_sum' => 'User.field_one+User.field_two+User.field_three'
    );

This doesn't work. I get the error 'field_one doesn't exist' when it gets to the 'field_sum'. I've asked before how to combine the three sql statements and haven't really gotten a satisfactory answer. It proved much better and easier to simply run them each alone and sum them after the fact. Is there any way to do that with in the context of CakePHP?

Edit

Here is cake's generated SQL:

SELECT 
    /* Users fields */
    (select coalesce(sum(coalesce(t_a.field, 0)), 0)*10 as field_one from t_a join t_b on t_a.t_b_id = t_b.id where t_b.user_id=User.id) AS `User__field_one`, 
    (select coalesce(sum(coalesce(t_c.field, 0)), 0)*2 as field_two from t_d left join (t_c) on (t_d.id=t_c.t_d_id) where t_d.user_id = User.id) AS  `User__field_two`, 
    (select coalesce(sum(coalesce(value, 0)), 0) as bonus_reputation from reputation_bonuses where user_id=User.id) AS  `User__field_three`,        (`User`.`field_one`+`User`.`field_two`+`User`.`field_three`) AS  `field_sum`, 
    FROM `users` AS `User`   
    WHERE `User`.`email` = '/* redacted */' AND `User`.`password` = '/* redacted */'    LIMIT 1 

Having seen that I tried changing the definition to (User__field_one+User__field_two+User__field_three) to take advantage of how they were named. No luck.

The exact error is: SQL Error: 1054 unknown column User.field_one in field list.

Answer 1

Just remove the alias :

'field_sum' => 'field_one + field_two + field_three'

Answer 2

I've done something similar in a model constructor, by recycling SQL snippets. Not the most efficient, but it might work. Something like:

function __construct($id = false, $table = null, $ds = null) {
    $snippet1 = 'select coalesce(sum(coalesce(t_a.field, 0)), 0)*10 as field_one from t_a join t_b on t_a.t_b_id = t_b.id where t_b.user_id=User.id';
    $snippet2 = 'select coalesce(sum(coalesce(t_c.field, 0)), 0)*2 as field_two from t_d left join (t_c) on (t_d.id=t_c.t_d_id) where t_d.user_id = User.id';
    $snippet3 = 'select coalesce(sum(coalesce(value, 0)), 0) as field_three from t_e where user_id=User.id';

    $this->virtualFields['field_one'] = $snippet1;
    $this->virtualFields['field_two'] = $snippet2;
    $this->virtualFields['field_three'] = $snippet3;

    $this->virtualFields['field_sum'] = $snippet1.' + '.$snippet2.' + '.$snippet3;

    parent::__construct($id, $table, $ds);
}