Question regarding chaining multiple any_of criteria #Mongoid

Question

I have a requirement where I need to run a MongoDB query like the following:

db.collection.find({ $or : [{"field1" : "value1"}, {"field2" : "value2"}], 
$or : [{"field3" : "value3"}, {"field4" : "value4"}]}) 

i.e.

(field1 == value 1 or field2 == value2) and (field3 == value3 or field4 
== value4) 

I want to achieve this through criteria chaining because the query gets formed dynamically from different parts of the code. But if I try doing something like the following

criteria = Collection.any_of({"field1" => "value1"}, {"field2" => 
"value2"})
criteria = criteria.any_of({"field3" => "value3"}, {"field4" => "value4"}) 

I get the resultant query where all these are combined into a single $or statement like

db.collection.find({ $or : [{"field1" : "value1"}, {"field2" : "value2"}, 
{"field3" : "value3"}, {"field4" : "value4"}]}) 

What is the way to achieve "and" of the two "any_of" using criteria chaining?

Pedigo

which version of Mongoid you use ? – Vaud

There is an issue with the way any_of / $or is handled by Mongoid. Have opened an issue on Github: github.com/mongoid/mongoid/issues/issue/569 – Pedigo

This answer helped - https://mcmap.net/q/797489/-mongoid-and-or-query – Variolite

Answer 1

You can do it with avoid any_of.

criteria = Collection.where('$or' => [{"field1" => "value1"}, {"field2" => "value2"}])
criteria = criteria.where('$or' => [{"field3" => "value3"}, {"field4" => "value4"}])

Vaud

Tried this. But it also yielded the same result where all the query fields got merged into a single "$or" query - similar to using any_of. – Pedigo

and you can success to do you request on MongoDB console ? – Vaud

In newer versions of Mongoid you can do Collection.or([{"field1" => "value1"}, {"field2" => "value2"}]).or({"field3" => "value3"}, {"field4" => "value4"}]). – Pluton

Answer 2

you can write this with mongoid 2.4.0:

  Collection.all_of({"$or" => [{"field1" => "value1"}, {"field2" => "value2"}]}, {"$or" => [{"field3" => "value3"}, {"field4" => "value4"}]})

Hayfield

This will most likely include too many documents. In my case, I want documents that satisfy BOTH $or conditions. – Soche

Collection.all_of adds a criterion that specifies expressions that must all match in order to return results. The corresponding MongoDB operation is $and. two.mongoid.org/docs/querying/criteria.html#all_of. So this should be something like ((field1==value1) OR (field2==value2)) AND ((field3==value3) OR (field4==value4)). – Hayfield

Answer 3

For Mongoid 3 and 4 there is a relatively nice way of chaining or criteria without merging.

TL;DR

MyModel.all_of(MyModel.xxx.selector, MyModel.yyy.selector)

Mongoid maintainer Durran showed the above trick in this Github issue: https://github.com/mongoid/mongoid/issues/2170

You can arbitrarily chain criteria using the same technique and some array trickery like this:

selectors = []
selectors << MyModel.search(term) if term
selectors << MyModel.some_any_of if some_condition
selectors << MyModel.some_other_any_of if some_other_condition
...
MyMode..all_of(*selectors)

Each row will add another and condition.

It may be worth noting too that you do not have to build each selector from the model. You can get an initial scope back from your permission system or something ans just call .selector on it before adding on more criteria.

Lenoralenore

Answer 4

It took me forever to get this right, but this is what worked for me:

scope :for_user, lambda {|user| 
    any_of(
      {recipient_id: Moped::BSON::ObjectId.from_string(user.id)}, 
      {sender_id: Moped::BSON::ObjectId.from_string(user.id)},
      {sender_email: user.email},
      {recipient_email: user.email}
    )
  }

You have to make sure to wrap the individual criteria for your $or condition in {} so that it nests the OR properly. (Using Mongoid3/Moped)

Knowles

thanks, that worked for me and keeps the search terms clear – Obliterate

Answer 5

This is still the case. I was able to work around this only by using the Ruby Mongo Driver API directly.

Soche