How use DATE_ADD in Yii $criteria?

Question

Model.php

// Declare $datetime_limit
public datetime_limit;

Controller.php

// datetime_limit should be the actual datetime + 5 days
$criteria->select="DATE_ADD(NOW(), INTERVAL 5 DAY) AS datetime_limit";

Error Message:

Active record "Users" is trying to select an invalid column "DATE_ADD(NOW()". Note, the column must exist in the table or be an expression with alias.

---

Edit 1:

I would like to filter the find w/ a condition using a relation table (Many to Many). So the datetime_limit cannot have in relational events.datetime. How can I do that?

$criteria->select=array("DATE_ADD(NOW(), INTERVAL 5 DAY) AS datetime_limit");
$criteria->with=array('events');
$criteria->having='datetime_limit!=`events`.`datetime`';
$models=Users::model()->findAll($criteria);

Answer 1

This exception is thrown in CActiveFinder::getColumnSelect.

When CDbCriteria::$select is a string, it's treated as a simple list of comma-delimited columns. Your expression gets interpreted as two distinct columns. You can work around this by setting select to an array yourself - in this case the comma splitting is not done¹:

$criteria = new CDbCriteria();
$criteria->select = array("DATE_ADD(NOW(), INTERVAL 5 DAY) AS datetime_limit");
$models = Users::model()->findAll($criteria);

Note that if you write an alias that doesn't correspond to a public model property or a DB field, it will be retrieved but silently ignored - for some reason Yii doesn't throw an exception for that.

---

¹ However, this function will still attempt to find a . in the expression and interpret the part after it as a column identifier - don't use . in your expression and you should be fine.