Yii Framework Forum: createCommand delete :not in where?? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

createCommand delete :not in where?? How can i do this?? Rate Topic: -----

#1 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 31 July 2012 - 09:20 AM

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))));


???
0

#2 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,600
  • Joined: 04-March 10
  • Location:UK

Posted 31 July 2012 - 09:29 AM

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

http://www.yiiframew...ondition-detail
0

#3 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 31 July 2012 - 09:44 AM

View PostKeith, on 31 July 2012 - 09:29 AM, said:

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


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);
?
0

#4 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,600
  • Joined: 04-March 10
  • Location:UK

Posted 31 July 2012 - 09:52 AM

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?
0

#5 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 31 July 2012 - 09:54 AM

DbCriteria represents a query criteria, such as conditions, ordering by, limit/offset. It can be used in AR query methods such as CActiveRecord::find and CActiveRecord::findAll.

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


0

#6 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,600
  • Joined: 04-March 10
  • Location:UK

Posted 31 July 2012 - 09:57 AM

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?
0

#7 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 31 July 2012 - 10:03 AM

View PostKeith, on 31 July 2012 - 09:57 AM, said:

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...
0

#8 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,600
  • Joined: 04-March 10
  • Location:UK

Posted 31 July 2012 - 10:14 AM

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.
0

#9 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 31 July 2012 - 10:23 AM

View PostKeith, on 31 July 2012 - 10:14 AM, said:

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.
0

#10 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,166
  • Joined: 05-January 12
  • Location:Morocco

Posted 31 July 2012 - 10:24 AM

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));

0

#11 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 01 August 2012 - 01:38 AM

View Postbennouna, on 31 July 2012 - 10:24 AM, said:

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
0

#12 User is offline   Gusarov 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 16-March 12

Posted 01 August 2012 - 02:10 AM

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).")) ");	


0

#13 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,600
  • Joined: 04-March 10
  • Location:UK

Posted 01 August 2012 - 02:55 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users