createCommand delete :not in where??

Yii 1.1 General Discussion
1

For example, i have some_table.id.

I define array with id’s that shouldn’t be deleted ($noDelete),

and want to delete rest where some_table.param = ‘1’.

How can i do this??


$noDelete = array(1,2,4,5,8,99,22,44);

$param = 1;

Yii::app()->db->createCommand()->delete('some_table', array(array('not in' , 'id' , $noDelete),array('param=:param' , array(':param'=>$param))));

???

2

If you’re able to use CDbCriteria, then you can use this:

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addNotInCondition-detail

3

So I should

create new instance of CDbCriteria class,

define


$conditions->addCondition("column", "where param='1'")->addNotInCondition("column",array($noDelete))

,

and then execute


Yii::app()->db->createCommand()->delete('some_table',$conditions);

[color="#666600"][size="2"]?[/size][/color]

4

I’m not sure if you can use CDbCriteria in that context, although the documentation doesn’t make it clear. Feel free to try it, but I’m guessing that you’ll get an error.

Are you not able to use ActiveRecord with this table?

5

[color=#222222][font=Arial, sans-serif][size=4]DbCriteria represents a query criteria, such as conditions, ordering by, limit/offset. [/size][/font][/color][color=#222222][font=Arial, sans-serif][size=4]It can be used in AR query methods such as CActiveRecord::find and CActiveRecord::findAll.[/size][/font][/color]

So I can’t use CDbCriteria for deleting from db.

6

Okay, sorry for the confusion, but it seems like you were using the right function in the first place. I’m so used to using AR that I didn’t even realise that function existed (I thought it was pseudo-code).

What exactly is going wrong with the code you originally posted?

7

It just don’t work, it deletes all table data. It seems it ignores all conditions. :(

Even when conditions are just "not in" or where…

8

Can you try this and confirm that it doesn’t delete incorrect records:




$noDelete = array(1,2,4,5,8,99,22,44);

Yii::app()->db->createCommand()->delete('some_table', array('not in', 'id', $noDelete));

I think your conditions array may be incorrectly specified.

9

This doesn’t work, I tried.

Now trying to solve problem with command builder, will reply when have get success.

10

According to http://www.yiiframework.com/doc/api/1.1/CDbCommand#delete-detail, shouldn’t it be:


$noDelete = array(1,2,4,5,8,99,22,44);

$param = 1;

Yii::app()->db->createCommand()->delete('some_table', 'param = :param AND id NOT IN :noDelete', array(':noDelete' => $noDelete, ':param'=>$param));

or


$noDelete = array(1,2,4,5,8,99,22,44);

$param = 1;

Yii::app()->db->createCommand()->delete('some_table', 'param = :param AND id != :noDelete', array(':noDelete' => $noDelete, ':param'=>$param));
11

first throws exception, second one deletes all table data

12

didn’t want to use this, but hadn’t find any other variants




Yii::app()->db->createCommand()->delete('some_table',"(param='".$param."') AND (id NOT IN(".implode(',',$noDelete).")) ");
13

I imagine that’s pretty much what the Yii implementation does. I don’t see a problem with doing it that way as long as the input array is trusted.

14

Hi!

You just need to add AND operator as first element of array and pass scalar parameter as third argument of delete-method.

Look this:




$noDelete = array(1,2,4,5,8,99,22,44);

$param = 1;

Yii::app()->db->createCommand()->delete('some_table', 

    array(

        'AND',        

        array('not in' , 'id' , $noDelete),

        array('param=:param' )

    ),

    array(':param'=>$param)

);

It’s work for me!

Have a nice day!