using an IN clause

Archives Yii 1.0.x Discussions
1

Hey all,

Not if this is actually a bug or perhaps the intended behaviour and I just don't understand why, but I figured someone might have some ideas…

Let's say I have this code:



$playerIds = array(1,2,3);


$criteria = new CDbCriteria();


$criteria->condition = 'Player.playerId IN (:playerIds)';


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


$players = Player::model()->findAll($criteria);

When I execute this, the corresponding SQL gets generated like this:



... WHERE Player.playerId IN ('1, 2, 3')

Note the quoted parameter '1, 2, 3'.  What I was hoping Yii would produce would be:



... WHERE Player.playerId IN (1, 2, 3)

After all, substituting the string '1, 2, 3' doesn't seem to make any sense when the substitution parameter is an array.  Am I missing something, or is this a problem others have experienced?  Don't get me wrong…  Its not an insurmountable problem or anything.  I know that there are at least two alternatives to handling this issue.  First, one can use the command builder as shown here:

http://www.yiiframew…3.msg11747.html)

Alternatively, one can just hardcode the IN clause like so:



$playerIds = array(1,2,3);


$playerIdsString = implode(',', $playerIds);


$criteria = new CDbCriteria();


$criteria->condition = 'Player.playerId IN (' . $playerIdsString . ')';


$players = Player::model()->findAll($criteria);

I would just think Yii would be much easier to work with if one could use an array as a parameter for an IN clause and it would implode the array as necessary.

Thoughts?

2

You cannot bind the IN parameter like this. This is not supported by PDO. You need to bind each IN number by itself, or directly generate the IN part.

3

Ah, well that certainly explains why it isn't working…  I'm not sure if its something that you can control through Yii (or you'd even want to), but I tend to think people might appreciate this as a feature where Yii improves upon its underlying PDO architecture.  Its a minor inconvenience to have to code out the IN clause every time, but it might be a nice feature to be able to bind to an array like this and have Yii generate the proper SQL.  Just my two cents.

Thanks for the info!  Keep up the great work!

4

You can use Yii::app()->db->schema->commandBuilder->createInCondition() to simplify the job a little bit.