Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongoose, how to do a find() with two or conditions

I'm trying to do a find which will get all documents which has a title or desc that contains the regular expression (which works) and then filter it further to only contain documents which either have the "private" flag set to false or the "user" field set to the given userId.

Here's what I'm trying so far...

FCSet.find({}, {"flashCards":0}).or([{ 'title': { $regex: re }}, { 'desc': { $regex: re }}]).or([{ 'private': false}, { 'user': 'userId'}]).sort('title')

This should only be returning 6 rows but it's returning 56. If I take out the 2nd or() then it works for filtering by title/desc. I'm guessing that I'm going about it the wrong way.

Can someone help me figure out how to do this correctly?

like image 570
Rocky Pulley Avatar asked Jan 22 '14 04:01

Rocky Pulley


2 Answers

You have to put the ORs into an AND:

FCSet.find( 
    { "flashCards": 0 }, 
    $and: [ 
        { 
            $or: [
                { 'title': { $regex: re } }, 
                { 'desc': { $regex: re } }
            ]
        },
        {
            $or: [ 
                { 'private': false }, 
                { 'user': 'userId' } 
            ]
        }
    ]
).sort('title')
like image 181
heinob Avatar answered Sep 28 '22 15:09

heinob


The answer by @heinob is totally correct however I implore you not to use it.

The biggest reason is that nested $or queries DO NOT USE INDEXES: https://jira.mongodb.org/browse/SERVER-3327

(in fact more specific to your case: https://jira.mongodb.org/browse/SERVER-6542 )

As such any index on that block will be utterly useless.

Instead you would be better off coding into your application a means to create a single $or block instead of doing what looks like the easiest way.

like image 28
Sammaye Avatar answered Sep 28 '22 16:09

Sammaye