Ordering Collection by multiple fields and condition in Mongoid

Question

I have 4 fields in a document which is name, online, like and score. I want to ordering by multiple fields and conditions collection of million documents with pagination.

Example some documents :

My user 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": 0 },

I will explain my point with the following example (example using an array).

Example in ruby language, I have an array structure looks like :

[["A", 1, 10, 1],
["B", 0, 9, 1],
["C", 0, 8, 1],
["D", 1, 8, 0],
["E", 1, 7, 1],
["F", 0, 10, 0]]

If online is 1 should be sort again by descending of like, but when online is 0 should be sort again by descending of score.

example sort :

list.sort{|a, b| a[1] == 1 ? ([-a[1], -a[2]] <=> [-b[1], -b[2]]) : ([-a[1], -a[3]] <=> [-b[1], -b[3]]) }

Result like this :

[["A", 1, 10, 1],
["D", 1, 8, 0],
["E", 1, 7, 1],
["B", 0, 9, 1],
["C", 0, 8, 1],
["F", 0, 10, 0]]

That is an array sort, but my problem is I have collection of mongodb and million documents, I can't use an array sort, because it will heavy load to database, should get all documents and convert to array (including sorting) and than paginate them, I think that's a bad idea.

I have try with order()/order_by() mongoid's optional method like :

User.
order_by([:online, :desc], [:like, :desc], [:score, :desc]).
hint(online: -1, like: -1, score: -1).
page(1).per(10)

But that query is only order by online and score, is there sort method in mongoid like an array sorting? or there is something like bubble sort in mongodb?

same problem here : Ruby on Rails: Concatenate results of Mongoid criterias and paging, merge method doesn't helped me because it can be replace the first criteria.

Answer 1

Using aggregation $cond

User.collection.aggregate([
      { 
        "$project" => {
            "id" => 1, 
            "name" => 1, 
            "online" => 1, 
            "like" => 1, 
            "score" => 1,
            "sort" => {
                "$cond" => {
                   "if" => { 
                      "$eq" => ["$online", 1] 
                   },
                   "then" => "$like",
                   "else" => "$score"
                }
            }
       }
     },
     { 
        "$sort" => {
            "online" => -1,  
            "sort" => -1,
            "id" => 1
        }
     },
     {
        "$skip" => 0
     { 
        "$limit" => 12 
     }
])

references :

1. https://docs.mongodb.com/manual/reference/operator/aggregation/cond/
2. https://www.oodlestechnologies.com/blogs/How-to-use-Conditional-Statements-for-sorting-data-in-MongoDB