Sort multiple condition MongoDB

Question

I have collection of user, and this is the following of documents :

{ "_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 }

I want to show the list of users with some of criterias and ordering with some conditons, online users and most liked in the top of the list,after online user list is show offline users with most scored & most liked. The following of rules :

If online is 1 must be sort by descending of like.
If online is 0 and score is 1 must be sort by descending of score.
If online is 0 and score is 0 must be sort by descending of like.

So, the result can be 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 }

I have finished until point 2, my query following :

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
}
])

But I have the current result following :

{ "_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 },

You can see, based on point 3, instance { "_id": 8, "name": "H", "online": 0, "like": 13, "score": 0 } should be on top with score is 0

I'm not sure about the need for the sort field in the project stage for your pipeline. However in the sort stage, you can write `$sort : { "online" : -1, "sort" : -1, "like" : -1 }` — Oluwafemi Sule, May 08 '18 at 04:53
@OluwafemiSule If I don't put `id` to sort stage, it will show duplucation issue https://stackoverflow.com/a/44693835/1297435, and I have try with `$sort : { "online" : -1, "sort" : -1, "like" : -1, "id": 1 }` but the `sort` criteria ignored — itx, May 08 '18 at 04:59
thanks for pointing that out. That'll be `$sort : { "online" : -1, "sort" : -1, "like" : -1, "_id": 1 }` — Oluwafemi Sule, May 08 '18 at 05:18
So what happens when **none** of those conditions actually apply? Do you default to "like"or "score"? You can actually clean up the logic without nesting the cond as far as I can see it, however presuming either of those is actually the default, then I see a different output sort order to both your current and your expected results. For instance `"H"` is `online: 0, like: 13` and your conditions lean towards "like", which puts that result on top. — Neil Lunn, May 08 '18 at 05:41
Yeah I think you need to read it through again. Your conditions would say that is actually the top result. Your expected results don't actually match the rules you state in the question. The easiest way is to simply remove the final projection and look at your projected `"sort"` values. So you nested `$cond` is not right, but the "three rules" are not correct for the expected output either. How about you actually write a detailed calculated result explaining why each of the ranking results is in it's place and the `"sort"` field value you are expecting. That would make it clear for everyone. — Neil Lunn, May 08 '18 at 06:56
Does my answer below work for you ? If something is wrong, can you comment for me ? — Bùi Đức Khánh, May 08 '18 at 07:25
@KhánhBùiĐức sorry, I will try one by one and tried edit my question to be clear. I will check it. — itx, May 08 '18 at 07:28
The logic still does not add up. This is not about you correcting your code in the question. What you need to do is show a "solid set of rules" like I've already stated twice. Don't just say "in this order" and instead explain for each item what the calculated sort value will be and therefore "why" it should be in that order. Because it still currently does not add up. — Neil Lunn, May 08 '18 at 07:36
@NeilLunn actually I can't explain how to calculate sort of value, and why I should to use order like that, I only have some logicals to explain what I need, I am sorry to make you confused. — itx, May 08 '18 at 08:31
How do you expect anyone to solve it if you cannot explain how it works then? Really the whole comment stream is for nothing if you cannot simply lay out the logic as why each element is returned in that specific order. Fix it or don't if you don't fix it then don't bring me into it. But your current responder is incorrect with their answer and if you won't spell out the rules plainly then you're not going to get an answer. Marking all the rest of the comments for clean up. — Neil Lunn, May 08 '18 at 08:35

Bùi Đức Khánh · Answer 1 · 2018-05-09T01:47:53.370

First create additional column call point with value (1 - online)*score

After this sort data by:

online desc
point desc (online = 1 pointbe always 0, online is 0 point is score)
like desc

You can use this query

    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 } }
            ]
        );

thanks for the answer, I have tried, the top of list are offline users.. — itx, May 08 '18 at 09:04
I have tried again, the offline users sorted by like only instead of `score` — itx, May 08 '18 at 10:08

score 0 · Answer 2 · answered May 09 '18 at 08:19

Please check below query :

db.getCollection('yourTable').aggregate([
{
   $project :
       {
           "id" : 1,
           "name" : 1,
           "online": 1,
           "like" : 1,
           "score" : 1,
           onlineSortLike: {
            $cond: {
              if: { $and: [{ $eq: ['$online',1 ] }] },
              then: '$like', 
              else: 0,
            },
          },
          sortOfflineScore: {
            $cond: {
              if: { $and: [{ $eq: ['$online',0] }] },
              then: '$score', 
              else: 0,
            },
          },
          sortOfflineScoreLike: {
            $cond: {
              if: { $and: [{ $eq: ['$online', 0] }] },
              then: '$like', 
              else: 0,
            },
          },


       }
},
{
   $sort :
       {
           "online" : -1,
           "onlineSortLike" : -1,
           "sortOfflineScore" : -1,
           "sortOfflineScoreLike" : -1
       }
},
{
   $skip : 0
},
{
   $limit : 9
}
])

Sort multiple condition MongoDB

2 Answers2