Using multiple where(or AND) which have nested or condition?

Question

How can one query in mongodb using Waterline ORM in Sailjs, with multiple AND condition each of them having nested OR conditions.

An Example MySQL query would be:

SELECT * FROM Users WHERE (score IS NULL OR activity IS NULL) AND (invited IS NULL OR invited_by IS NULL)

Using multiple .where() doesn't seems to be working, also using all condition in OR prop destroy the logic.

Any solutions around?

Answer 1

I am not sure if it is within the scope of the your expected solution but why don't .query() method .

Users.query('SELECT * FROM Users WHERE (score IS NULL OR activity IS NULL) AND (invited IS NULL OR invited_by IS NULL)', [ ] ,function(err, result) {
  if (err) { // do something }
  else {// do something else with the result}


});

P.S. : I have not run this query directly . There may be some syntax error on my part.

Please look into this page for more reference http://sailsjs.com/documentation/reference/waterline-orm/models/query

EDIT : Sorry , I misunderstood the question , However with mongoDB you have to use the native() method . It should be Something like

var queryCriteria = {
   "$and" : [{"$or" : [{ "score" : null },{"activity" : null}] },{"$or" : [{ 
 "invitedby" : null },{"invited" : null}] } ]
} 

Then you can use the native method like this

Users.native(function(error, collection) {
  if (error) return res.serverError(error);

  collection.find(queryCriteria ,{}).toArray(function (err, results) {
    if (err) return res.serverError(err);
    return res.ok(results);
  });
});

P.P.S : MongoDB does not have a schema . So if you have not entered null explicitly while creating data , the field will not be created i.e the data may not have a score field at all , In that case you must replace null will {$exists : false} . Also I have used this native method before , In my very limited experience it is pretty slow .. I remember getting a transactionPerSecond of only around 10 in one of my cases . Please see to it .

Link for .native() http://sailsjs.com/documentation/reference/waterline-orm/models/native