PHP+MongoDB dynamic queries - adding conditional operators

Question

I am making use of dynamic queries in mongoDB, by using them this way:

$query['clicks'] = array('$gt' => 6);
$query['lang'] = "de";

$cursor = $collection->find($query);

The question is pretty simple and straightforward: how can one add 'OR' conditionals? for example to have both 'lang' to be "de" OR "fr";

Because right now, Mongo implies that there is an AND between "clicks" and "lang". So how can i structure clicks > 6 && ( lang == 'de' || lang == 'fr' )

score 1 · Answer 1 · edited Oct 05 '12 at 09:32

          $cond=array();
          $cond=array_merge($cond,array("clicks" => array('$gt' =>6)));
          $cond=array_merge($cond,array("$or" => array(array("lang" =>'de'),array("lang" =>'fr'))));

    $cursor = $collection->find($cond);

----------------------------

    another way is : 
       $cond=array();
          $cond=array_merge($cond,array("clicks" => array('$gt' =>6)));
          $cond=array_merge($cond,array("$in" => array('de','fr')));

    $cursor = $collection->find($cond);

GBD · Accepted Answer · 2012-10-05T11:11:15.040

0

You can give try as below

$query['clicks'] = array('$gt' => 6);
$query['$or'] = array(array('lang' => 'de'), array('lang' => 'fr'));
$cursor = $collection->find($query);

How to add $in operator

$query['clicks'] = array('$gt' => 6);
$query['$or'] = array(array('lang' => 'de'), array('lang' => 'fr'));
$query['_id'] = array('$in' => array(new mongoId('506d95bc5c73952c14000002'), new mongoId('506d95bc5c73952c14000003')));

$cursor = $collection->find($query);

edited Oct 05 '12 at 11:11

answered Oct 05 '12 at 09:07

GBD

15,847
2
46
50

thank you. you get the right answer for simplicity. Just a quick one tho: `$query['$or'] = array(array('lang' => 'fr'), array('lang' => 'de')); $query['$or'] = array(array('_id' => new mongoId('506d95bc5c73952c14000002')));` how can I "add" the second 'or' to the first? the way it is now, it just overwrites it. I know it's out of the scope of the question but, well.. – john smith Oct 05 '12 at 09:19
do you want clicks > 6 && ( lang == 'de' || lang == 'fr' || _id=='506d95bc5c73952c14000002') this way now ? – GBD Oct 05 '12 at 09:37
yes, but not exactly that. I would like to know how I can dynamically adding more more ORs (I will need to loop though some IDs and add them. It doesn't really have much to do with the question but, if you can help, it'd be appreciated. that new or doesn't have to be inside the brakets – john smith Oct 05 '12 at 09:43
is it necessary to use OR ? you can use $in operator for list of _id – GBD Oct 05 '12 at 09:58
oh, you're right!! didn't remember there was $in, aha. Thank you for the answer! it was great help :) – john smith Oct 05 '12 at 10:01
but, could "$in" be good for this? I need both $in AND the $or results. – john smith Oct 05 '12 at 10:04
$in gives same result as $or.. good practice to use $in. you can also use $in & $or both at the same time – GBD Oct 05 '12 at 10:15
well, I seems a bit weird because, if I do: `$query['$or'] = array(array('lang' => 'de'), array('lang' => 'fr')); $query['$in'] = array(array('_id' => new mongoId('506d95bc5c73952c14000002')));` it returns nothing. while if I put the mongoId in the '$or' it does work... strange. – john smith Oct 05 '12 at 11:03
then way you did i think it is wrong. let me give you example in my answer. – GBD Oct 05 '12 at 11:07
this still doesn't work. I need to comment out '$or' in order to make it work. basically its like: if I have $in, it will look for that ID only if it's in 'fr' or 'de' the code is the same as yours. – john smith Oct 05 '12 at 11:36
perhaps it's important to underline that this ID is NOT contained in any of those resulting ORs. – john smith Oct 05 '12 at 11:50
this way, it works, although that's not very dynamic: `$query['$or'] = array(array('sex' => 'fm'), array('sex' => 'f*'), array('_id' => array('$in' => array(new mongoId('506d95bc5c73952c14000002')))) );` – john smith Oct 05 '12 at 12:06

score 0 · Answer 3 · answered Oct 05 '12 at 10:20

$or is good but not extremely index friendly especially when you start getting into more diverse ranges. A slightly more index friendly method is with $in:

$db->collection->find(array('clicks' => array('$gt' => 6), 
    'lang' => array('$in' => array('fr', 'de'))));

That tends to make more efficient use of indexes at times.

Also note that nested $ors do not currently use indexes however $or can use multiple index plans which means that a query like so:

$db->collection->find(array('clicks' => array('$gt' => 6), '$or' => array(
    array('lang' => 'fr'), arrray('lang' => 'de')
))

Would be able to use multiple indexes but in this case it is useless since it only going over one field so better to use $in.

PHP+MongoDB dynamic queries - adding conditional operators

3 Answers3