How to 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. 

---

Change 1:

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

 $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 is treated as a simple list of comma-separated columns. Your expression is interpreted as two different columns. You can get around this by setting select to the array yourself - in this case, comma separation is not performed ¹ :

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

Please note that if you write an alias that does not match the public model object or the database field, it will be retrieved, but silently ignored - for some reason Yii will not throw an exception for this.

---

¹ However, this function will still try to find . in the expression and interpret the part after it as the column identifier - do not use . in your expression, and you should be fine.