Using mysql vaiables in CDbCriteria

I tried to do something like this in the Model->search() method:

$criteria->select = array('t.*', "@totalPrice := SUM(IF(c.presale_date IS NULL, c.price, IF(c.presale_date <= '".$this->bookingDate."', c.presale_price, c.price) ) ) AS totalPrice","(@totalPrice/COUNT(c.space_id)) AS pricePerNight");

Unfortunately I don’t think it worked because it shows pricePerNight as 0 eventhough totalPrice is not zero.

Did Criteria get rid of my mysql variables? How can I work around this?

On another note, you may have noticed the $this->bookingDate in the sql. It’s not safe. Is there a way to use arguments inside the SELECT part of criteria?

Use CDbExpression for your purpose:

$criteria->select = new CDbExpression(…)