mongodb - $or 多参数查询

Question

我正在努力实现以下目标

SELECT *
FROM users
WHERE   (_currentMatch = null AND
        unregistrated = false AND
        gender = 'male' AND
        lastMatchTime < dateMale)
OR      (_currentMatch = null AND
        unregistrated = false AND
        gender = 'female' AND
        lastMatchTime < dateFemale);

Mongoose :

User.find(
    {
        $or:[{
                lastMatchTime: {$lt: dateMale},
                _currentMatch: null,
                unregistrated: false,
                gender: 'male'
            },
            {
                lastMatchTime: {$lt: dateFemale},
                _currentMatch: null,
                unregistrated: false,
                gender: 'female'
            }
        ]
    }
)

此查询似乎不符合所有给定条件。我是否缺少 $and？

Answer 1

看到前两个条件 (_currentMatch = null AND unregistrated = false) 在两个 OR 子句中都是重复的，您的 SQL 查询可以重写为:

SELECT *
FROM users
WHERE   
    _currentMatch = null 
    AND unregistrated = false 
    AND gender in ('female', 'male')

等效的 mongo 查询如下:

User.find({
    "_currentMatch": null,
    "unregistrated": false,
    "gender": { "$in": ["female", "male"] }
})

---

-- 更新--

SELECT *
FROM users
WHERE   (_currentMatch = null AND
        unregistrated = false AND
        gender = 'male' AND
        lastMatchTime < dateMale)
OR      (_currentMatch = null AND
        unregistrated = false AND
        gender = 'female' AND
        lastMatchTime < dateFemale);

可以重写为

User.find({
    "_currentMatch": null,
    "unregistrated": false,
    "$or": [
        { "gender": "female", "lastMatchTime": { "$lt": dateFemale } },
        { "gender": "male", "lastMatchTime": { "$lt": dateMale } }
    ]
})