parameter binding to CDbExpression('NOW()') fails

Yii 1.1 General Discussion
1

What is the proper way to bind a parameter to an expression, like ‘NOW()’? CDbExpression doesn’t seem to work for me. I’m trying to create a simple parameterized named scope for my models.




public function after($minDate=NULL) {

  if (is_null($minDate))

    $minDate = new CDbExpression('NOW()'); 

  $c = new CDbCriteria; // CDbCommand?

  $c->compare('time','>=:minDate');

  $c->params[':minDate'] = $minDate;

  // this fails too (it just quotes the NOW() function)

  // $c->params[':minDate'] = ''.$minDate; 

  $this->getDbCriteria()->mergeWith($c);

  return $this; }
2

Maybe this will work:




  $c = new CDbCriteria; // CDbCommand?

  $c->compare('time','>=:' . $minDate);

  $this->getDbCriteria()->mergeWith($c);

  return $this;

but I’m not sure. Also you can try this:




public function after($minDate=NULL) {

  $c = new CDbCriteria;

  if (is_null($minDate))

    $c->addCondition('time >= now()');

  else

    $c->compare('time','>=:' . $minDate);

  $this->getDbCriteria()->mergeWith($c);

  return $this; }
3

Thanks @maschingan,

Unfortunately, like you suspected, your first fix didn’t work. Yii does parameter binding and assumes a string when you use compare() to compose a condition. Seems odd that Yii would insist on quoting a CDbExpression which should never be escaped. I tried using ‘(NOW())’ as well – the parentheses are supposed to prevent quoting, but that didn’t work either.

Your second approach works fine, but I found I couldn’t use the colon near the inequality. If you don’t have named parameter to match a string you put in the compare function, Yii thinks the colon is part of the string and puts it in the value of the parameter. Without the colon, Yii still automatically creates a bind variable for you (:ycp0) and the quoted datetime string doesn’t get prepended with colon. So here’s what worked:




public function after($minDate=NULL) {

	$c = new CDbCriteria;

	if (is_null($minDate))

		$c->addCondition('time >= NOW()');

	else

		$c->compare('time','>='.$minDate );

	$this->getDbCriteria()->mergeWith($c);

	return $this; }

It comes in pretty handy as a named scope for a lot of models.