Yii Framework Forum: HAS_ONE relation to return derived model? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

HAS_ONE relation to return derived model? Rate Topic: -----

#1 User is offline   gazbond 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 61
  • Joined: 02-November 09
  • Location:London, UK

Posted 06 May 2011 - 09:05 AM

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?
0

#2 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 06 May 2011 - 09:30 AM

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#3 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 2,601
  • Joined: 10-October 10
  • Location:Denmark

Posted 06 May 2011 - 09:55 AM

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.
"Less noise - more signal"
0

#4 User is offline   Asgaroth 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 371
  • Joined: 17-July 09
  • Location:Bogota, Colombia

Posted 06 May 2011 - 11:02 AM

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...
- Extension: Dynamic DataProvider Behavior
- Extension: XUpload - jQuery File Upload Extension
- Extension: PhpQuickProfiler - A Web Log Router that will help you profile your application
- Extension: XDateView - A Date grouped Grid View
- Extension: Foundation - An easy to use, powerful, and flexible framework for building prototypes and production code on any kind of device.



- Wiki: How to generate Yii like Documentation
- Wiki: How to re enable logging during unit testing
0

#5 User is offline   gazbond 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 61
  • Joined: 02-November 09
  • Location:London, UK

Posted 06 May 2011 - 12:11 PM

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!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users