HAS_ONE relation to return derived model?

Yii 1.1 General Discussion
1

I have 2 models:




Donation:

-id

-currency

-gross

-fee

-charity_id


Charity:

-id

-name

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

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




-totals:

      array(

            array('currency'=>'GBP','total_gross'=>102.31,'total_fee'=>23.01),

            array('currency'=>'USD','total_gross'=>232.51,'total_fee'=>63.39)

      )

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:




'total_gross'=>array(self::STAT,'Donation','charity_id',

      'select'=>'SUM(gross)',

),

'total_fee'=>array(self::STAT,'Donation','charity_id',

      'select'=>'SUM(fee)',

)

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?

2

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.

/Tommy

3

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.

4

What about something like this:





'total_gross'=>array(self::STAT,'Donation','charity_id',

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

),

'total_fee'=>array(self::STAT,'Donation','charity_id',

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

)

untested…

5

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:




$db->createCommand()

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

      ->from(preg_replace('/{{(.*?)}}/',$db->tablePrefix.'\1',$this->tableName()))

eek!