2

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

Darren Burgess
  • 4,200
  • 6
  • 27
  • 43
sleeper
  • 518
  • 4
  • 9

2 Answers2

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.

dcrosta
  • 26,009
  • 8
  • 71
  • 83
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)
pingw33n
  • 12,292
  • 2
  • 37
  • 38
  • OK ... I didn't understand that although that operation were chainable, they were not really combinable. On the other hand, looking at http://stackoverflow.com/a/6751102/745918, it seems that this is the recommanded construction when you want to retrieve the last X messages. – sleeper Dec 12 '11 at 13:40
  • @sleeper that particular answer is misleading. Never take anything here as 100% truth, especially from users with low reputation. – pingw33n Dec 12 '11 at 14:09