Databases
StackOverflow2013
Postsdbo
POChaining time-based sort and limit issue
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChaining time-based sort and limit issue
    text
    copied!<p>Lately I've encountered some strange behaviours (i.e. meaning that they are, IMHO, counter-intuitive) while playing with mongo and sort/limit.</p> <p>Let's suppose I do have the following collection:</p> <pre><code>&gt; 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") } </code></pre> <p>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)</p> <p>Naively I was about running something along:</p> <pre><code>db.fred.find({time: {$lte: ISODate("2011-12-04T00:00:00Z")}}).sort({time: -1}).limit(2).sort({time: 1}) </code></pre> <p>but it does not work the way I expected:</p> <pre><code>{ "_id" : ObjectId("..."), "record" : 1, "time" : ISODate("2011-12-01T00:00:00Z") } { "_id" : ObjectId("..."), "record" : 2, "time" : ISODate("2011-12-02T00:00:00Z") } </code></pre> <p>I was thinking that the result would have been record 2, record 3 and 4.</p> <p>From what I recollected, it seems that the 2 sort does apply before limit:</p> <pre><code> sort({time: -1}) =&gt; record 4, record 3, record 2, record 1 sort({time: -1}).limit(2) =&gt; record 4, record 3 sort({time: -1}).limit(2).sort({time: 1}) =&gt; record 1, record 2 </code></pre> <p>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.</p> <p>What is my mistake here and how can I achieve the expected behavior?</p> <p>BTW: running mongo 2.0.1 on Ubuntu 11.01</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload