search

louischatriot / nedb

The JavaScript Database, for Node.js, nw.js, electron and the browser
MIT License
13.48k stars 1.02k forks source link

How to search multiple feilds' "$and"? #681

Closed lanistor closed 3 years ago

lanistor commented 3 years ago

Such as ((A>1 && C>1) or (A<5)) && ((C>1 && D<1 ) or ( C<5 )). For only field "A" case, we can use 

db.find({ 
  $or: [
    { A: { $gt: 1 }, B: {$gt: 1} },
    { A: { $lt: 5 } },
  ],
});

But with field "B", we cannot use:

db.find({ 
  $or: [
    { A: { $gt: 1 }, B: {$gt: 1} },
    { A: { $lt: 5 } },
  ],
  $or: [
    { C: { $gt: 1 }, D: {$lt: 1} },
    { C: { $lt: 5 } },
  ],
});

So, how we can do this?

Jose134 commented 3 years ago

I'm not sure why your solution isn't working but a workaround is to AND both conditions like this:

db.find({
    $and: [
        { $and: [ { A: { $gt: 1 } }, { A: { $lt: 5 } }, ]},
        { $and: [ { B: { $gt: 1 } }, { B: { $lt: 5 } }, ]}
    ]
});

Alternatively you can make it cleaner by putting everything in the same $and instead of nesting:

db.find({
    $and: [
        { A: { $gt: 1 } },
        { A: { $lt: 5 } },
        { B: { $gt: 1 } },
        { B: { $lt: 5 } }
    ]
});
lanistor commented 3 years ago

I'm not sure why your solution isn't working but a workaround is to AND both conditions like this:

db.find({
    $and: [
        { $and: [ { A: { $gt: 1 } }, { A: { $lt: 5 } }, ]},
        { $and: [ { B: { $gt: 1 } }, { B: { $lt: 5 } }, ]}
    ]
});

Alternatively you can make it cleaner by putting everything in the same $and instead of nesting:

db.find({
    $and: [
        { A: { $gt: 1 } },
        { A: { $lt: 5 } },
        { B: { $gt: 1 } },
        { B: { $lt: 5 } }
    ]
});

It was meant to make the example a bit simpler, but it was ambiguous. In our real query scenario, it will be more complicated. I have modified my sample code.

Jose134 commented 3 years ago

This should match the updated query:

db.find({
    $and: [
        {$or: [
            { $and: [
                { A: { $gt: 1 } },
                { C: { $gt: 1 } }
            ]},
            {
                A: { $lt: 5 }
            }
        ]},
        {$or: [
            { $and: [
                { C: { $gt: 1 } },
                { D: { $lt: 1 } }
            ]},
            {
                C: { $lt: 5 }
            }
        ]}
    ]
});

It's not the prettiest thing ever but I've done a bit of testing and it seems to work properly

lanistor commented 3 years ago

It's not the prettiest thing ever but I've done a bit of testing and it seems to work properly

Got it. Thanks a lot.