mongo sort after limit after sort - Not working

问题:

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)

回答1:

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.

回答2:

How about using skip():

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

回答3:

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.)

回答4:

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.

回答5:

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
    ),
));