tuga
(Tuga74)
January 26, 2010, 12:02pm
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
tri
(tri - Tommy Riboe)
January 26, 2010, 3:52pm
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
tuga
(Tuga74)
January 26, 2010, 6:48pm
3
tri:
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
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()"?
tri
(tri - Tommy Riboe)
January 26, 2010, 11:05pm
4
tuga:
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()"?
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
tuga
(Tuga74)
January 27, 2010, 12:14am
5
tri:
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
You are my hero… Thanks a lot Tommy, worked very well