Does the where method have an "NOT IN" condition?

☆ A M B ☆
2,475 Posts

Everett Reply #1, 15 years, 9 months ago

I’m trying to emulate a MySQL statement like this:

SELECT * FROM my_table WHERE id NOT IN (1,2,3);

Is there an xPDO equivalent? Or is the best thing to do this:

$query->where(array('id:!=' => 1));
$query->where(array('id:!=' => 2));
$query->where(array('id:!=' => 3));

And an added bonus question... Oracle has a limit of 500 of how many values can be specified in an "IN" condition like this... does MySQL too?

Thanks!

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 9 months ago

Quote from: Everett at Jan 13, 2011, 06:46 PM

I’m trying to emulate a MySQL statement like this:
SELECT * FROM my_table WHERE id NOT IN (1,2,3);
Is there an xPDO equivalent?

$criteria->where('id:NOT IN' => array(1,2,3));

Quote from: Everett at Jan 13, 2011, 06:46 PM

And an added bonus question... Oracle has a limit of 500 of how many values can be specified in an "IN" condition like this... does MySQL too?

No idea tbh, but I am aware of a recent bug with sending a large number of values like this via PDO in general, and well, it’s just a bad practice in general if you have a large set of specific values.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
2,475 Posts

Everett Reply #3, 15 years, 9 months ago

Thanks... yeah, it’s not the best idea, but I’d rather send a set of values to delete like that rather than issuing umpteen many individual delete statements.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/