Using PHP and MongoDB sorting results based on key AND value?

Question

For my case i am using MongoDB to store a whole bunch of products from 50 countries, entry in DB looks something like this:

{
    _id: xxxxxxxxxxxxxxx,
    country: 'au',
    title: 'Equipment X',
    price: 1000,
},
{
    _id: xxxxxxxxxxxxxxy,
    country: 'us',
    title: 'Equipment Y',
    price: 1000,
},
...
...

I would like to know if it is at all possible to query all products and sort by country from au FIRST, then the rest of the products after that. The simple MongoDB sort seems to only sort by key. If it is possible, does it involve map/reduce?

Edit: A more detailed example

Documents in collection:

{ country:'cn', title:'Equipment A', price:'100'},
{ country:'au', title:'Equipment B', price:'100'},
{ country:'za', title:'Equipment C', price:'100'},
{ country:'us', title:'Equipment D', price:'100'},
{ country:'nz', title:'Equipment E', price:'100'},
{ country:'it', title:'Equipment F', price:'100'},
{ country:'nz', title:'Equipment G', price:'100'},
{ country:'au', title:'Equipment H', price:'100'},

I am looking for a sort that will return products from nz to be at the top and then follow by all other products, like this:

{ country:'nz', title:'Equipment E', price:'100'},
{ country:'nz', title:'Equipment G', price:'100'},
{ country:'au', title:'Equipment B', price:'100'},
{ country:'au', title:'Equipment H', price:'100'},
{ country:'cn', title:'Equipment A', price:'100'},
{ country:'it', title:'Equipment F', price:'100'},
{ country:'us', title:'Equipment D', price:'100'},
{ country:'za', title:'Equipment C', price:'100'},

Eve Freeman · Accepted Answer · 2012-01-18 08:49:26Z

2

By sorting on a key, it means sorting on the values in a key. This is what you want, I think. (Left off the beginning part to connect to the db and pick a collection).

$cursor = $collection->find();
$cursor->sort(array('country' => 1))

update So is this what you want to do?

$cursor = $collection->find(array('country' => 'nz'));
// process your nz products...

$cursor = $collection->find(array('country' => array( '$neq' => 'nz' )));
// process the rest of your products...

update2 Well, I think you may need to resort to doing something like this, if you really need this functionality.

// create a new field on the records that don't have it yet
$collection->update(
   array('country' => 'nz', array('first' => array('$exists' => false))), 
   array('$set' => array('first'=>1)), 
   array('multiple' => true));
// ensure index on that field
$collection->ensureIndex(array('first' => 1));
// find everything
$cursor = $collection->find();
// sort on that field
$cursor->sort(array('first':-1));

edited Jan 18, 2012 at 8:49

answered Jan 18, 2012 at 3:07

Eve Freeman

33.3k5 gold badges89 silver badges104 bronze badges

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

5 Comments

Bundeeteddee Over a year ago

Hi there! That will only sort the country alphabetically. If i would like to sort it to have products from nz first, then the rest will follow after, it won't work with just key sorting.

Eve Freeman Over a year ago

I'm not quite sure I understand what you're trying to do. Why can't you just do two queries, if you want to prioritize on a country. Or you could have another key/value called "countryOrder:0" for NZ, and then, where you specify the real order you want countries to appear.

Bundeeteddee Over a year ago

Two queries will require me to stitch the results together, and i lose out on using skip() and limit() for paging purposes. I've just edited the question to have a more detailed(?) example. Sorry, having a tough time explaining what i'm trying to do.

Bundeeteddee Over a year ago

Yes i think so too, definitely one of the possible options. Its not that messy either, i.e. i wont be adding 30 new properties for each document to denote 30 countries, just 1 extra on each. Thanks @WesFreeman for the help!

Eve Freeman Over a year ago

You only really need to add the extra property on the nz documents--the rest don't need it at all. And you can do it incrementally and update them en masse. It's not too bad, but a bit more messy than you might like. It's probably faster than the aggregation stuff coming out, though--once the "first" field is there the update won't be doing anything.

user2665694 · Accepted Answer · 2012-01-18 03:05:26Z

0

The simple MongoDB sort seems to only sort by key

Truly uninformed nonsense.

MongoDB can sort by multiple criteria. A simple look at the basic sorting documentation would have been useful here.

http://www.mongodb.org/display/DOCS/Sorting+and+Natural+Order

answered Jan 18, 2012 at 3:05

user2665694

1 Comment

Bundeeteddee Over a year ago

multiple key yes, not value, yet. I understand you can sort by something like {country:1, title:1} and it will come back with the sorting with that order. What i am looking for is sorting like {country:"au", title:1}. Not possible at this point but possibly in next mongo release. [blog.mongodb.org/post/16015854270/…

Collectives™ on Stack Overflow

Using PHP and MongoDB sorting results based on key AND value?

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related