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?
(tri - Tommy Riboe)
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'
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')