Calculated Fields in AR

Yii 1.1 General Discussion
1

Hi all!

I have this query


SELECT hotel.id,

       hotel.name,

       hotel.stars,

       hotel.desc_overview,

       city.id,

       city.name,

       (hotel.rate_min / currency.value) AS rate_euros

FROM hotel

     INNER JOIN city ON (city.id = hotel.city_id)

     INNER JOIN country ON (country.id = city.country_id)

     INNER JOIN currency ON (currency.id = country.currency_id)

WHERE hotel.feat_date >= NOW()

LIMIT 5

Can I have this part “(hotel.rate_min / currency.value) AS rate_euros” returned when I do ???




$criteria = new CDbCriteria( );

		$criteria->select = array(

			'id', 

			'name', 

			'desc_overview', 

			'stars', 

			'rate_min');

		$criteria->condition = 'feat_date >= NOW()';


return $this->with( array(

			'city' => array(

				'select' => 'city.name, city.id'), 

			'country.currency' => array(

				'select' => 'value'), 

			'hotel_images' => array(

				'select' => 'hotel_images.id', 

				'group' => 'hotel_id')) )->findAll( $criteria );

Of course I can get all the fields from the tables and then calculate, but the point is that I want to order by that value.

Thanks

2

I believe this could work:




//return $this->with( array(

return Hotel::model()->with( array(

  'city' => array(

    'select' => 'city.name, city.id'

  ), 

  'country.currency' => array(

    'select' => array('value', 't.rate_min / value) AS rate_euros')

    'order' => 'rate_euros',

  ), 

  'hotel_images' => array(

    'select' => 'hotel_images.id', 

    'group' => 'hotel_id'

  )

) )->findAll( $criteria );

(partially tested/not tested)

/Tommy

3

Thank you Tommy, that worked very well, I tried something similar but I guess I was doing something wrong.

Another issue, related to this one, can I have the field "rate_euros" as part of the returned array when I do "findAll()"?

4

It seems like you need to declare rate_euros in the Currency model




public $rate_euros;

And to access the value, use -> instead of the dot notation used in the call to with(), e.g.




echo $models[$i]->country->currency->rate_euros;

(partially tested)

/Tommy

5

You are my hero… Thanks a lot Tommy, worked very well