Delete Query Not Completing

Yii 1.1 General Discussion
1

I am running a MySQL DELETE command with Yii::app()->db->createCommand()->execute(), but the query I expect it to run seems to only partially complete. I get no error messages or warnings in the logs.

Here is the simplified code from my Container model:




$itemIds = array('10','11','12','13');


$command = Yii::app()->db->createCommand(

   'DELETE FROM container_items WHERE container_id=:container_id AND item_id IN (:item_ids)'

);


$command->execute(array(

   ':item_ids' => implode(",", $itemIds),

   ':container_id' => $this->id,

));

After this code is executed, the row with the model’s container_id and item_id of ‘10’ will be deleted, but the other rows (‘11’,‘12’,‘13’) will be left untouched.

I assume the generated SQL is incorrect, but I can’t figure out how to view it. If I enable profiling, I see this:

system.db.CDbCommand.execute(DELETE FROM container_items WHERE container_id=:container_id AND item_id IN (:item_ids))

2

why you can not use FIND_IN_SET? like…


Containeritems::model()->deleteAll("container_id='".$container_id."' AND FIND_IN_SET ($itemIds,item_ids)");

or


  DELETE FROM container_items WHERE container_id=:container_id AND FIND_IN_SET IN ($itemIds,:item_ids)
3

Try out Yii’s standard function deleteAllByAttributes


Containeritems::model()->deleteAllByAttributes(array('container_id'=> array('10','11','12','13')));
4

I’m not sure how FIND_IN_SET gains me anything. Seems like if the query isn’t getting constructed correctly with IN, it won’t get constructed correctly with FIND_IN_SET either.

deleteAllByAttributes is interesting, but I don’t want to delete my items, just their relationship with the container.

After some more detailed digging, it looks like PDO may not like a parameter that contains commas. I may need to dynamically add placeholders for each item in the "IN ()" area and pass the array of items with the container_id pushed into the first position. So, something like:


$itemIds = array('10','11','12','13');

$place_holders = implode(',', array_fill(0, count($itemIds), '?'));


$command = Yii::app()->db->createCommand(

   "DELETE FROM container_items WHERE container_id=? AND item_id IN ($place_holders)"

);


$command->execute(array_merge(array($containerId),$itemIds));
5

One of the weekenesses of parameter binding in strings ( not using the AR ) is that you can not to key in ( :param ) because it’ll come out key in ( ‘1,2,3,4,5’ ) … notice the inappropriate placing of the quotes?

To fix it you either need to do an inline key in (" . implode ( … ) . ")

or you need to refactor your usage as they’ve described above.

6

Try with CDbCriteria::addInCondition

e.g.:




$criteria = new CDbCriteria();

$criteria->compare('container_id', $containerId);

$criteria->addInCondition('item_id', array(10,11,12,13));

// or: $criteria->compare('item_id', array(10,11,12,13));


$command = Yii::app()->db->commandBuilder->createDeleteCommand('container_items', $criteria);

echo $command->getText(); // DELETE FROM `container_items` WHERE (container_id=:ycp0) AND (item_id IN (:ycp1, :ycp2, :ycp3, :ycp4))

$command->execute();
7

The placeholder technique I mentioned earlier worked, but CDbCriteria::addInCondition() is the best solution to my question.