mongo sort after limit after sort - Not working

Question

I have a collection, from which i get particular type of users using $query
Then I need sort them according to user_id ascending and limit them to 2000

From these I need the max user_id, so I sort them in descending order and limit to 1.

But this second sort forgets the limit of 2000 and sorts over over the entire cursor from find().

Any work-around?

$cursor = $collection   ->find($query)                // too many entries
            ->sort(array('user_id'=>1))   // go ascending
            ->limit(2000)                // got our limited qouta
            ->sort(array('user_id'=>-1)) // go descending to get max
            ->limit(1);          // the one with max(user_id)

You have a semicolon on the third line. Is that a copy/paste error or is it also present in your original code? — Arjan
– Arjan, Commented Jan 20, 2013 at 10:39
oh yes, copy paste error, thanks for pointing out, corrected — davneetnarang
– davneetnarang, Commented Jan 20, 2013 at 10:42

Sammaye · Accepted Answer · 2013-01-20 15:15:26Z

7

Your cannot do a sort and then a limit and then a sort. The Cursor object is exactly that and it will not run the query until you iterate to the first result via getNext() which is either run manually or within a foreach loop not only that but sort is just a property of the object as such making two sorts just overwrites the property.

The best way to achieve what your looking for is:

$doc = $collection->find($query)->sort(array('user_id' => 1))
       ->skip(1999)->limit(1)->getNext();

That will always pick the highest user_id (which occurs at the end in this sort) of the group, which will give off the same results as doing two sorts.

edited Jan 20, 2013 at 15:15

answered Jan 20, 2013 at 14:45

Sammaye

44k7 gold badges110 silver badges149 bronze badges

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

3 Comments

davneetnarang Over a year ago

this one looked like correct. I tried collection->find->sort->skip(1999)->limit->getNext as well as collection->find->sort->skip(1999)->getNext. Both are giving same (but incorrect) answer. Will post back when i figure it out.

davneetnarang Over a year ago

we can do $collection->find()->sort()->skip(1999)->getNext(); the limit(1) in between can be definitely skipped

Sammaye Over a year ago

@davneet Yea I didn't think of that, getNext() of course just gets the next document so the limit is inert there.

JohnnyHK · Accepted Answer · 2013-01-20 15:14:49Z

3

How about using skip():

$cursor = $collection   ->find($query)  
        ->sort(array('user_id'=>1))   
        ->limit(2000)                
        ->skip(1999);

edited Jan 20, 2013 at 15:14

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

answered Jan 20, 2013 at 11:52

StefanMK

1,3231 gold badge13 silver badges23 bronze badges

3 Comments

JohnnyHK Over a year ago

+1 Wouldn't a limit(1) here work just as well since only the first of the docs after the 1999 skipped ones is needed?

davneetnarang Over a year ago

the above code still gives me 2000 records in the cursor... that would mean ill have to iterate over all those to get to the last record

StefanMK Over a year ago

ups, sorry. find(...)->sort(...)->skip(1999)->limit(1) is probably what you need.

Eren Güven · Accepted Answer · 2013-01-20 11:20:00Z

0

What is the reason behind sort-limit-sort-limit approach?

Can't you just do

$cursor = $collection ->find($query)
            ->sort(array('user_id'=>-1))
            ->limit(1);

EDIT: Also, only the last sort applied to the cursor has an effect. (This line is present in pymongo docs here, which makes sense.)

edited Jan 20, 2013 at 11:20

answered Jan 20, 2013 at 11:11

Eren Güven

2,39419 silver badges27 bronze badges

2 Comments

davneetnarang Over a year ago

the point of first sort-limit is to get the first 2000 records out of all. the second sort-limit simply gets the max out of these 2000. Ur code will give me max out of all records from find()

Eren Güven Over a year ago

Ok, now I get it. You're trying to get the 2000th document in ASC order that matches the query. You should know that when you apply sort on a cursor. No documents are retrieved (therefore sorted) until you actually iterate over that cursor. You can use skip but that becomes a slow operation. Best approach is to use range based queries in situations like this.

Bas Peeters · Accepted Answer · 2014-11-07 08:56:54Z

0

I am using the following code in php5 and mongodb latest build:

$doc = $collection->find($query)->sort(array('user_id' => 1))
    ->skip(1999)->limit(1)->getNext();

It stops working when I use ->skip(1999)->limit(1) after sort()

The cursor $doc does give me values . I tried braking it down into this:

$doc = $collection->find($query)->sort(array('user_id' => 1))
$doc = $doc->skip(1999)->limit(1);

That worked. May be you should try that in new versions.

edited Nov 7, 2014 at 8:56

Bas Peeters

3,3644 gold badges37 silver badges50 bronze badges

answered Nov 7, 2014 at 8:29

Sak90

5906 silver badges19 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:17:01Z

0

Answer by Sammaye is correct.

You can still achieve the way you wanted. You can use aggregation framework, as it executes one stage after other stage and so on.

$doc = $collection->aggregate(array(
    array(
      '$match' => $query,
    ),
    array(
      '$sort' => array(
          'user_id'=> 1
      ),
    ),
    array(
      '$limit' => 2000
    ),
    array(
      '$sort' => array(
          'user_id'=> -1
      ),
    ),
    array(
      '$limit' => 1
    ),
));

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Sep 2, 2016 at 12:15

Somnath Muluk

58.3k39 gold badges225 silver badges230 bronze badges

Collectives™ on Stack Overflow

mongo sort after limit after sort - Not working

5 Answers 5

3 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related