CODEWITHSUNDEEP
node.jsmongodbexpresssails.js
Keith John Hutchison
Keith John Hutchison

Reputation: 5277

How to get an 'and' query working with sails-mongodb

I've got one query that work within a mongo shell

db.getCollection('insights').find({$and:[{author:/jim/i}]})

returns 2 records.

The 'or' code calling waterline/sailsjs-mongo 

db('insights').find({
    or: [
        {or: [ {author: new RegExp( ".*"+"jim"+".*", 'i' )} ] }
    ]
    }).limit(req.params.limit).skip(req.params.offset).exec(function (err, insights) { ... }

returns 2 records as expected.

If I change the 'or' to an 'and'

db('insights').find({
    or: [
        {and: [ {author: new RegExp( ".*"+"jim"+".*", 'i' )} ] }
    ]
    }).limit(req.params.limit).skip(req.params.offset).exec(function (err, insights) { ... }

I get 0 records which is not expected. It should return 2 records.

I wrote some code using the native mongodb javascript client.

var MongoClient = require('mongodb').MongoClient,
  test = require('assert');
// Connection url
var url = 'mongodb://localhost:27017/infosystem';
// Connect using MongoClient
MongoClient.connect(url, function(err, db) {
  // Create a collection we want to drop later
  var col = db.collection('insights');
  // Show that duplicate records got dropped
  col.find({$and:[{author: new RegExp('.*Jim.*','i')}]}).toArray(function(err, items) {
    test.equal(null, err);
    test.equal(2, items.length);
    db.close();
  });
});

Which when run throws no assertions.

 node test_insights.js

mongodb is version 3.2.9, sails-mongodb and waterline is version "0.12.1"

What is the correct way to call a mongodb $and query within sails-mongodb?

Upvotes: 1

Views: 818

Answers (1)

DarkLeafyGreen
DarkLeafyGreen

Reputation: 70406

https://docs.mongodb.com/manual/reference/operator/query/and/#and

$and performs a logical AND operation on an array of two or more expressions (e.g. expression1, expression2, etc.) and selects the documents that satisfy all the expressions in the array. The $and operator uses short-circuit evaluation. If the first expression (e.g. expression1) evaluates to false, MongoDB will not evaluate the remaining expressions.

So in your case it does not even make sense to use the AND operator because you also can use an implicit AND, like

db.getCollection('insights').find({author:/jim/i})

To come to your actual problem. I cannot reproduce this issue using native mongodb. In shell:

db.collection.insert({name:'Foo', age:28})
db.collection.insert({name:'Bar', age:32})

db.collection.find()
{ "_id" : ObjectId("57ca84c374f2776cc983ad9e"), "name" : "Foo", "age" : 28 }
{ "_id" : ObjectId("57ca84cd74f2776cc983ad9f"), "name" : "Bar", "age" : 32 }

db.collection.find({$and:[{name:new RegExp('.*Fo.*','i')}]})
{ "_id" : ObjectId("57ca84c374f2776cc983ad9e"), "name" : "Foo", "age" : 28 }

> db.collection.find({$or:[{$or:[{name:new RegExp('.*Fo.*','i')}]}]})
{ "_id" : ObjectId("57ca84c374f2776cc983ad9e"), "name" : "Foo", "age" : 28 }

> db.collection.find({$or:[{$and:[{name:new RegExp('.*Fo.*','i')}]}]})
{ "_id" : ObjectId("57ca84c374f2776cc983ad9e"), "name" : "Foo", "age" : 28 }

I get the expected result. However when I try to use waterline queries with nested and/or I see the same problem that you describe. Let's have a look at following native query:

db.collection.find({$or:[{$and:[{name:'Foo'}, {age:28}]}, {name:'Bar'}]})

which correctly returns:

{ "_id" : ObjectId("57ca84c374f2776cc983ad9e"), "name" : "Foo", "age" : 28 }
{ "_id" : ObjectId("57ca84cd74f2776cc983ad9f"), "name" : "Bar", "age" : 32 }

Now lets create the same query in waterline:

Test.find({
    or: [
        {and: [{name:'Foo'},{age:28}]},
        {name: 'Bar'}
    ]
})

this returns []. The relevant code responsible for the and/or query can be found here: https://github.com/balderdashy/sails-mongo/blob/master/lib/query/index.js#L132

I am sure the nesting of and/or operators is simply not supported by sails-mongo but I was not able to find any evidence. You could file a feature request on their github page.

As a workaround you can use the native underlaying mongodb adapter to create your queries. As a downside your code will not be database agnostic.

Upvotes: 2

Related Questions