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?