CODEWITHSUNDEEP
mongodb
want_to_be_calm
want_to_be_calm

Reputation: 1647

mongodb sort with skip and limit not sort the record according to index

I am trying to do pagination with Mongo using skip and limit.

I want to get the page records sorted by register_time. In the database, the records are sorted by index and not by register_time.

How can I make multiple pages of records (multiple skips) follow the same register_time order ?

db.collection.aggregate(
[ { "$project" : { "os" : "$os", 
                   "register_time" : "$register_time", 
                   "channel" : "$channel", 
                   "event" : "$event", 
                   "user_id" : "$user_id" } }, 
   { "$match" : { "register_time" : 
                { "$gt" : ISODate("2016-06-23T00:00:00Z"), 
                  "$lt" : ISODate("2050-06-25T23:59:00Z") }, 
                  "event" : "Register_with_number", 
                  "channel" : "001" } }, 
   { "$group" : { "_id" : 
                  { "register_time" : "$register_time", 
                    "user_id" : "$user_id", 
                    "os" : "$os", 
                    "channel" : "$channel" }, 
                  "count" : { "$sum" : 1 } } },
   {"$skip":4},
   {"$limit":10}, 
   { "$sort" :  {"_id.register_time" : -1 } } ])

And here is the skip result

 { "_id" : { "register_time" : ISODate("2016-06-24T08:49:36Z"), "user_id" : "65675f96", "os" : "Android", "channel" : "040401" }, "count" : 1 }
 { "_id" : { "register_time" : ISODate("2016-06-24T06:29:56Z"), "user_id" : "f61d0572", "os" : "Android", "channel" : "040401" }, "count" : 1 }
 { "_id" : { "register_time" : ISODate("2016-06-24T04:13:31Z"), "user_id" : "d7d1349d", "os" : "Android", "channel" : "040401" }, "count" : 1 }
 { "_id" : { "register_time" : ISODate("2016-06-24T03:40:13Z"), "user_id" : "ecea2908", "os" : "Android", "channel" : "040401" }, "count" : 1 }

And here is the second skip result:

{ "_id" : { "register_time" : ISODate("2016-06-24T09:05:13Z"), "user_id" : "6fde06a6", "os" : "Android", "channel" : "040401" }, "count" : 1 }
{ "_id" : { "register_time" : ISODate("2016-06-24T07:47:46Z"), "user_id" : "1e5e5712", "os" : "Android", "channel" : "040401" }, "count" : 1 }
{ "_id" : { "register_time" : ISODate("2016-06-24T05:34:55Z"), "user_id" : "47dfaa32", "os" : "Android", "channel" : "040401" }, "count" : 1 }
{ "_id" : { "register_time" : ISODate("2016-06-24T05:15:03Z"), "user_id" : "70960ae2", "os" : "Android", "channel" : "040401" }, "count" : 1 }

As you can see, the two page's register_time are not following the same order.

How can I make different pages (skips) follow one consistent order? Thanks.

Upvotes: 4

Views: 8717

Answers (2)

andrew.fox
andrew.fox

Reputation: 7951

The order matters.

Remember that $sort must be before $skip and $limit. If sort is after them, weird results might be returned. And if you want to add lookups, the $lookup element should be at the end.

Example:

aggregate([
  { "$match" : { "type" : "xyz" } }, 
  { "$sort" : { "createdDateTime" : -1 } }, 
  { "$skip" : 50 }, 
  { "$limit" : 10 }, 
  { "$lookup" : {....

Upvotes: 1

profesor79
profesor79

Reputation: 9473

you need to move $sort before $limit and $skip

as doing $limit you are limiting random documents from collection and next pipeline entries are based on random input

Upvotes: 11

Related Questions