HAS_ONE relation to return derived model?

I have 2 models:










I would like to display Charities with totals for each currency.

Therefore I would like to add the following derived property to Charity:






Which I can do with some custom SQL as follows:

SELECT currency, SUM(gross) as total_gross, SUM(fee) as total_fee FROM tbl_donation WHERE charity_id = :id GROUP BY currency

If I wasn’t concerned about currency I could create STAT relations as follows:







And then eager loading would reduce the amount of queries needed when rendering a list of Charities.

So my question is:

Is it possible to use a HAS_ONE relation to return a derived model that contains the totals i.e. a model that does not have a corresponding table?

I have not tried this, but the first thing I came to think of is to use your sql in the join property of a CDbCriteria.


If you have a fixed set of currencies, then you can use self::STAT, otherwise you need to do something different - maybe in the afterFind function?

Create some fields in your model, then populate them in your afterFind.

But, if you can use stats:

'usdGross' => array(self::STAT, 'Donation', 'donation_id', 'condition' => 'currency=USD', 'select' => 'SUM(amount)'),

Not tested at all! :)

If you only have like four different currencies, go for stat.

What about something like this:


      'select'=>'SUM(gross)', 'group' => 'total_gross.currency'



      'select'=>'SUM(fee)', 'group' => 'total_fee.currency'



Thanks all.

I have gone with using afterSave() in Donation to execute my custom SQL and using a de-normalized ‘totals’ field in Charity.

First time using the new query builder :)

Why no get table name with prefix method in active record?

Had to use the following:


      ->select('currency, SUM(gross) as gross')