gusarov
(Gusarov)
July 31, 2012, 2:20pm
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))));
Keith
(Kburton)
July 31, 2012, 2:29pm
2
gusarov
(Gusarov)
July 31, 2012, 2:44pm
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]
Keith
(Kburton)
July 31, 2012, 2:52pm
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?
gusarov
(Gusarov)
July 31, 2012, 2:54pm
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.
Keith
(Kburton)
July 31, 2012, 2:57pm
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?
gusarov
(Gusarov)
July 31, 2012, 3:03pm
7
Keith:
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?
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…
Keith
(Kburton)
July 31, 2012, 3:14pm
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.
gusarov
(Gusarov)
July 31, 2012, 3:23pm
9
Keith:
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.
This doesn’t work, I tried.
Now trying to solve problem with command builder, will reply when have get success.
abennouna
(Abennouna)
July 31, 2012, 3:24pm
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));
gusarov
(Gusarov)
August 1, 2012, 6:38am
11
According to http://www.yiiframew …d#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));
first throws exception, second one deletes all table data
gusarov
(Gusarov)
August 1, 2012, 7:10am
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).")) ");
Keith
(Kburton)
August 1, 2012, 7:55am
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.
dice4x4
(Dice The Hacker)
November 26, 2014, 1:49am
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!