MasudM MasudM - 3 months ago 12
Node.js Question

Combining $or and $and MongoDB

How would you do the following sql in Mongo?

SELECT * FROM messages WHERE (sender='me' AND recipient='them') OR (sender='them' AND recipient='me')


psuedocode example of query:

(sender == me && recipient == them) || (sender == them && recipient <= me)


I am trying to get all messages which I ('me') have sent to 'them' OR the other way round (all messages which I have received from 'them').

I cannot get $or and $and to work nicely together...

I am using Mongo 3.2.8 and Mongoose (with Node.js)

Answer

I tried.. created a sample dataset below and looks like it works

try this

> db.messages.insert({"sender":"me","recipient":"them"})
WriteResult({ "nInserted" : 1 })
> db.messages.insert({"sender":"them","recipient":"me"})
WriteResult({ "nInserted" : 1 })
> db.messages.insert({"sender":"a","recipient":"b"})
WriteResult({ "nInserted" : 1 })
> db.messages.insert({"sender":"me","recipient":"b"})
WriteResult({ "nInserted" : 1 })
> db.messages.insert({"sender":"them","recipient":"b"})
WriteResult({ "nInserted" : 1 })

check the collection

> db.messages.find();
{ "_id" : ObjectId("57b78b2c8d2d515b0309868e"), "sender" : "me", "recipient" : "them" }
{ "_id" : ObjectId("57b78b3a8d2d515b0309868f"), "sender" : "them", "recipient" : "me" }
{ "_id" : ObjectId("57b78b418d2d515b03098690"), "sender" : "a", "recipient" : "b" }
{ "_id" : ObjectId("57b78b468d2d515b03098691"), "sender" : "me", "recipient" : "b" }
{ "_id" : ObjectId("57b78b4c8d2d515b03098692"), "sender" : "them", "recipient" : "b" }

Query

> db.messages.find({"$or":[{"sender":"me","recipient":"them"},{"sender":"them","recipient":"me"}]});

output

{ "_id" : ObjectId("57b78b2c8d2d515b0309868e"), "sender" : "me", "recipient" : "them" }
{ "_id" : ObjectId("57b78b3a8d2d515b0309868f"), "sender" : "them", "recipient" : "me" }

>

Comments