CODEWITHSUNDEEP
node.jsmongodbmongoose
Rax Wunter
Rax Wunter

Reputation: 2767

Sort and distinct in mongoose

My mongoose query is:

Spread.find(findCase).where('loc').near({
        center: {
            type: 'Point',
            coordinates: [self.lon, self.lat]
        },
        maxDistance: distance
}).sort({ts : -1}).distinct("postId").exec();

So I get the error:

Error: sort cannot be used with distinct

But if I pass the query with console

db.spreads.distinct({}).sort({ts: -1});

That is ok.

So why mongoose doesn't let me to select distinct and sort in one query and how can I do it?

Upvotes: 3

Views: 9544

Answers (3)

Victor Cordeiro Costa
Victor Cordeiro Costa

Reputation: 2184

If you want to keep a previous ordering while applying distinct, you need to go with the approach of mongo.aggregate and you need to apply the $sort before and after applying $group.

So, for example, if you want to keep the sorting criteria of updated_at column in a descending order while applying the distinct of the name column in your collection, you should do as following:

db.collection.aggregate([
  { $sort: { updated_at: -1 } }, // Sort documents by updated_at Descending
  {
    $group: {
      _id: "$name", // Group Documents by name
      updated_at: { $first: "$updated_at" } // Capture most recent updated_at
    }
  },
  { $sort: { updated_at: -1 } }, // Sort groups by updated_at descending (need to be sorted again because the $group removes the sort on its output)
  { $project: { name: "$_id", _id: 0 } } // Project the desired output by filling "name" with the result of "_id" (created from the $group clause) and removes the "_id" afterwards
])
  • Output Example
[
  { "name": "Rax Wunter" },
  { "name": "Victorcorcos" },
  { "name": "William James" }
]

Upvotes: 0

Rax Wunter
Rax Wunter

Reputation: 2767

Test data:

db.createCollection("test");

db.test.insert({
    loc : {type: 'Point', coordinates : [42,42]},
    ts : new Date(2014,2,5),
    postId : 1
});

db.test.insert({
    loc : {type: 'Point', coordinates : [42,42]},
    ts : new Date(2014,2,5),
    postId : 1
});

db.test.insert({
    loc : {type: 'Point', coordinates : [42,42]},
    ts : new Date(2014,2,4),
    postId : 2
});

db.test.insert({
    loc : {type: 'Point', coordinates : [42,42]},
    ts : new Date(2014,2,3),
    postId : 3
});

With query

db.test.aggregate([
{
    $geoNear: {
    near : { type: 'Point', coordinates: [ 42, 42 ] },
    distanceField : 'dist.calculated',
    maxDistance: 200,
    spherical: true
    }
},
{
    $sort : {ts: -1}
},
{$group:{"_id":"$postId"}}
]);

Gives wrong result

{ "_id" : 3 }
{ "_id" : 2 }
{ "_id" : 1 }

so I guess mongo firstly applied grouping and then can't sort by absent field. For this reason probably mongoose prohibits use distinct with sorting.

Upvotes: 0

BatScream
BatScream

Reputation: 19700

From the docs, sort cannot be used with distinct.

Cannot be used with distinct()

But you can perform an aggregation operation:

Spread.aggregate(
{$geoNear:{
  "near":{"type":"Point","coordinates":[self.lon, self.lat]},
  "distanceField":"dist.calculated",
  "maxDistance":distance,
  "query":findcase,
  "spherical": true
}},
{$sort:{"ts":-1}},
{$group:{"_id":"$postId"}},function(err,resp){
  console.log(resp);
  // handle response.
}
)

Note: A 2dsphere Index needs to exist on the collection over the loc field. To create an Index, refer: Does applying a 2dsphere index on a mongoose schema force the location field to be required?.

Upvotes: 4

Related Questions