Chaining time-based sort and limit issue

Question

Lately I've encountered some strange behaviours (i.e. meaning that they are, IMHO, counter-intuitive) while playing with mongo and sort/limit.

Let's suppose I do have the following collection:

> db.fred.find()
{ "_id" : ObjectId("..."), "record" : 1, "time" : ISODate("2011-12-01T00:00:00Z") }
{ "_id" : ObjectId("..."), "record" : 2, "time" : ISODate("2011-12-02T00:00:00Z") }
{ "_id" : ObjectId("..."), "record" : 3, "time" : ISODate("2011-12-03T00:00:00Z") }
{ "_id" : ObjectId("..."), "record" : 4, "time" : ISODate("2011-12-04T00:00:00Z") }
{ "_id" : ObjectId("..."), "record" : 5, "time" : ISODate("2011-12-05T00:00:00Z") }

What I would like is retrieving, in time order, the 2 records previous to "record": 4 plus record 4 (i.e. record 2, record 3 and record 4)

Naively I was about running something along:

db.fred.find({time: {$lte: ISODate("2011-12-04T00:00:00Z")}}).sort({time: -1}).limit(2).sort({time: 1})

but it does not work the way I expected:

{ "_id" : ObjectId("..."), "record" : 1, "time" : ISODate("2011-12-01T00:00:00Z") }
{ "_id" : ObjectId("..."), "record" : 2, "time" : ISODate("2011-12-02T00:00:00Z") }

I was thinking that the result would have been record 2, record 3 and 4.

From what I recollected, it seems that the 2 sort does apply before limit:

  sort({time: -1})                          => record 4, record 3, record 2, record 1
  sort({time: -1}).limit(2)                 => record 4, record 3
  sort({time: -1}).limit(2).sort({time: 1}) => record 1, record 2

i.e it's like the second sort was applied to the cursor returned by find (i.e. the whole set) and then only, the limit is applied.

What is my mistake here and how can I achieve the expected behavior?

BTW: running mongo 2.0.1 on Ubuntu 11.01

dcrosta · Accepted Answer · 2011-12-12 13:10:04Z

3

The MongoDB shell lazily evaluates cursors, which is to say, the series of chained operations you've done results in one query being sent to the server, using the final state based on the chained operations. So when you say "sort({time: -1}).limit(2).sort({time: 1})" the second call to sort overrides the sort set by the first call.

To achieve your desired result, you're probably better off reversing the cursor output in your application code, especially if you're limiting to a small result set (here you're using 2). The exact code to do so depends on the language you're using, which you haven't specified.

answered Dec 12, 2011 at 13:10

dcrosta

26.4k8 gold badges74 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sleeper Over a year ago

OK, understodd my mistake :) Thanks for the explanation !

pingw33n · Accepted Answer · 2011-12-12 13:10:16Z

3

Applying sort() to the same query multiple times makes no sense here. The effective sorting will be taken from the last sort() call. So

sort({time: -1}).limit(2).sort({time: 1})

is the same as

sort({time: 1}).limit(2)

answered Dec 12, 2011 at 13:10

pingw33n

12.6k2 gold badges39 silver badges38 bronze badges

2 Comments

sleeper Over a year ago

OK ... I didn't understand that although that operation were chainable, they were not really combinable. On the other hand, looking at stackoverflow.com/a/6751102/745918, it seems that this is the recommanded construction when you want to retrieve the last X messages.

pingw33n Over a year ago

@sleeper that particular answer is misleading. Never take anything here as 100% truth, especially from users with low reputation.

Collectives™ on Stack Overflow

Chaining time-based sort and limit issue

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related