CODEWITHSUNDEEP
mongodbphp-mongodb
Nick ONeill
Nick ONeill

Reputation: 7379

Proper indexing and sorting with MongoDB

I have a relatively complex query that I'm running. I've included it below, but essentially I have a location query combined with an $or and $in query all to be sorted by date/time. Whenever I run the full query though it returns in ascending order rather than descending.

If I pull out the location part of the query, it successfully returns the results in the proper order, but once I put the location component of the query back in, it continues to fail.

Here's the query as written in php:

$query = array( 
        '$or' => array(
            array( ISPUBLIC => true ), 
            array( GROUP_INVITES.".".TO.".".INVITE_VAL => array( '$in' => $user_groups ) )
        ),
        LOC => array( '$near' => array( (float)$lon , (float)$lat ), '$maxDistance' => 1 )  //Using 1 degree for the max distance for now
    );

$res = $this->db->find( $query )->sort(array('ts'=>-1))->limit($limit)->skip($start);

The fields used are constants that map to fields in the database. My first assumption was that the indexes are not configured properly, but how should I have this indexed when combining geospatial queries with others?

UPDATE

I've been able to replicate the problem by simplifying the query down to:

$query = array( 
    HOLLER_GROUP_INVITES.".".HOLLER_TO.".".INVITE_VAL => array( '$in' => $user_groups ),
    HOLLER_LOC => array( '$near' => array( (float)$lon , (float)$lat ), '$maxDistance' => 1 )
);

UPDATE 2

I've run additional tests and it appears that when limit is removed it's sorted in the correct direction. No idea what's going on here.

Upvotes: 2

Views: 557

Answers (1)

Nick ONeill
Nick ONeill

Reputation: 7379

This question appears to have the answer: Sorting MongoDB GeoNear results by something other than distance? .... apparently $near was already performing a sort. If you simply want to limit within a given boundary but sort by something else, you should use $within instead of $near.

Upvotes: 1

Related Questions