mongodb - 排序多个条件MongoDB

Question

我有用户的集合，这是以下文件:

{ "_id": 1, "name": "A", "online": 1, "like": 10, "score": 1 },
{ "_id": 2, "name": "B", "online": 0, "like": 9, "score": 0 },
{ "_id": 3, "name": "C", "online": 0, "like": 8, "score": 1 },
{ "_id": 4, "name": "D", "online": 1, "like": 8, "score": 0 },
{ "_id": 5, "name": "E", "online": 1, "like": 7, "score": 1 },
{ "_id": 6, "name": "F", "online": 0, "like": 10, "score": 1 },
{ "_id": 7, "name": "G", "online": 0, "like": 5, "score": 0 },
{ "_id": 8, "name": "H", "online": 0, "like": 13, "score": 0 }
{ "_id": 9, "name": "I", "online": 0, "like": 6, "score": 0 }

我想显示具有一些条件的用户列表，并在列表顶部显示在线用户和最喜欢的用户列表，在线用户列表显示得分最高和最喜欢的离线用户。规则如下:

1. 如果 online 是 1 必须按 like 的降序排序。
2. 如果online 为0 且score 为1 必须按score< 的降序排序。
3. 如果 online 是 0 并且 score 是 0 必须按 like< 的降序排序。

所以，结果可能是这样的:

{ "_id": 1, "name": "A", "online": 1, "like": 10, "score": 1 },
{ "_id": 4, "name": "D", "online": 1, "like": 8, "score": 0 },
{ "_id": 5, "name": "E", "online": 1, "like": 7, "score": 1 },
{ "_id": 6, "name": "F", "online": 0, "like": 10, "score": 1 },
{ "_id": 3, "name": "C", "online": 0, "like": 8, "score": 1 },
{ "_id": 8, "name": "H", "online": 0, "like": 13, "score": 0 }
{ "_id": 2, "name": "B", "online": 0, "like": 9, "score": 0 },
{ "_id": 9, "name": "I", "online": 0, "like": 6, "score": 0 },
{ "_id": 7, "name": "G", "online": 0, "like": 5, "score": 0 }

我已经完成了第 2 点，我的查询如下:

db.users.aggregate([
{
   $project :
       {
           "id" : 1,
           "name" : 1,
           "online: 1,
           "like" : 1,
           "score" : 1,
           "sort" : {
               $cond:
                   {
                       "if" :
                           {
                               $eq : ["$online", true]
                           },
                       "then" : "$like",
                       "else" : "$score"
                   }
           }
       }
},
{
   $sort :
       {
           "online" : -1,
           "sort" : -1,
           "id" : 1
       }
},
{
   $skip : 0
},
{
   $limit : 9
}
])

但我有以下当前结果:

{ "_id": 1, "name": "A", "online": 1, "like": 10, "score": 1 },
{ "_id": 4, "name": "D", "online": 1, "like": 8, "score": 0 },
{ "_id": 5, "name": "E", "online": 1, "like": 7, "score": 1 },
{ "_id": 6, "name": "F", "online": 0, "like": 10, "score": 1 },
{ "_id": 3, "name": "C", "online": 0, "like": 8, "score": 1 },
{ "_id": 2, "name": "B", "online": 0, "like": 9, "score": 0 },
{ "_id": 7, "name": "G", "online": 0, "like": 5, "score": 0 },
{ "_id": 8, "name": "H", "online": 0, "like": 13, "score": 0 }
{ "_id": 9, "name": "I", "online": 0, "like": 6, "score": 0 },

可以看到，根据第3点，instance { "_id": 8, "name": "H", "online": 0, "like": 13, "score": 0 } 应该在顶部 score 是 0

Answer 1

首先创建附加列调用点，值为(1 - online)*score

在此之后对数据进行排序:

1. 在线 desc
2. point desc (online = 1 point一直为0，online为0 point为score)
3. 喜欢 desc

你可以使用这个查询

    db.yourtable.aggregate(
            [ 
                { $project:{
                    "id" : 1,
                    "name" : 1,
                    "online": 1,
                    "like" : 1,
                    "score" : 1,
                    point: { $multiply: [ 
                                    {$subtract: [1,"$online"]}                      
                                    , "$score"
                            ]}

                    }
                }
                ,{ $sort : { online: -1, point : -1, like : -1 } }
            ]
        );