Using Cdbexpression With Cdbcriteria In Findall

Yii 1.1 General Discussion
1

I am trying to generate and execute the following sql through CActiveRecord:




SELECT * FROM `bucket` `t` 

WHERE bkt_user = unhex('A4FF2131E00C4696837689FCAAAC7DD2');

I came up with this:




$uuid = 'A4FF2131E00C4696837689FCAAAC7DD2';

$criteria = new CDbCriteria();

$expression = new CDbExpression(

			'unhex(:value)', 

			array(':value'=>$uuid,));

$criteria->addCondition("bkt_user = :exp");

$criteria->params = array(':exp' => $expression);

$buckets = Bucket::model()->findAll($criteria);

A little debugging revealed that the snippet above generates the following sql:




SELECT * FROM `xpg_bucket` `t` WHERE bkt_user = :exp

and AFAIK only one CDbCommandBuilder::bindValue where ‘:exp’ is bound to unhex(:value). There is no parameter binding done for ‘:value’. This is happening in CDbCommandBuilder::createFindCommand. CDbCommandBuilder::createInsertCommand, on the other hand seems to take care of this condition.

CDbExpression’s documentation contains:


 * CDbExpression is mainly used in {@link CActiveRecord} as attribute values.

 * When inserting or updating a {@link CActiveRecord}, attribute values of

 * type CDbExpression will be directly put into the corresponding SQL statement

 * without escaping.

Does this mean CDbExpression is not meant to be used with CDbCriteria in findAll()? If yes, what is the alternative?

2

Try this

$criteria->addCondition('bkt_user = ’ . $expression);

3

I’ve tried this too. If I use this, I get:




CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was: SELECT * FROM `bucket` `t` WHERE user = unhex(:value)
4

Check that you’re not overriding params array like this:

$criteria->params = array(’:exp’ => $expression);

Do

$criteria->params[’:value’] = $uuid;

to add to params array (instead of overriding existing bound params).

5

Gosh! Thanks for the idea. It revealed that instead of doing:




$criteria->addCondition("bkt_user = :exp");

$criteria->params = array(':exp' => $expression);

I could do:




$criteria->addCondition('bkt_user = ' . $expression);

$criteria->params = array(':value'=>Yii::app()->user->getId());

This works, though feels odd. $expression is evaluated as ‘unhex(:value)’, leaving us to only specify ‘:value’ in $criteria->params.

This further leads me to:




$criteria->addCondition('bkt_user = unhex(:value)');

$criteria->params = array(':value'=>Yii::app()->user->getId());

Which also works. Oddly enough no CDbExpression is not involved at all.

Would you know what’s going on here? I was using my original approach because the same thing (‘bkt_user = unhex(:value)’) does not work in the insert context.

6

Topic moved to General Discussion.