1

I am trying to obtain the most recently created 3 accounts in the nearby area. So I tried to use a geo-spatial query combined with a sort(created_at: -1) and limit(3)

The basic geo-spatial query:

db.users.find({"loc": {$near: [28.41, 77.04], $maxDistance:0.05, $uniqueDocs: true}})
{ "created_at" : ISODate("2013-12-11T07:58:34.927Z"), "name" : "A" }
{ "created_at" : ISODate("2014-03-08T10:00:17.921Z"), "name" : "B" }
{ "created_at" : ISODate("2014-03-13T08:28:46.285Z"), "name" : "C" }
{ "created_at" : ISODate("2014-03-05T12:01:34.199Z"), "name" : "D" }
{ "created_at" : ISODate("2014-03-13T08:16:22.913Z"), "name" : "E" }
{ "created_at" : ISODate("2014-03-13T10:23:02.660Z"), "name" : "F" }

Adding sorting to it gave:

db.users.find({"loc": {$near: [28.41, 77.04], $maxDistance:0.05, $uniqueDocs: true}}).sort({created_at: -1})
{ "created_at" : ISODate("2014-03-13T10:23:02.660Z"), "name" : "F" }
{ "created_at" : ISODate("2014-03-13T08:28:46.285Z"), "name" : "C" }
{ "created_at" : ISODate("2014-03-13T08:16:22.913Z"), "name" : "E" }
{ "created_at" : ISODate("2014-03-08T10:00:17.921Z"), "name" : "B" }
{ "created_at" : ISODate("2014-03-05T12:01:34.199Z"), "name" : "D" }
{ "created_at" : ISODate("2013-12-11T07:58:34.927Z"), "name" : "A" }

And finally adding a limit gave:

db.users.find({"loc": {$near: [28.41, 77.04], $maxDistance:0.05, $uniqueDocs: true}}).sort({created_at: -1}).limit(3)
{ "created_at" : ISODate("2014-03-13T08:28:46.285Z"), "name" : "C" }
{ "created_at" : ISODate("2014-03-08T10:00:17.921Z"), "name" : "B" }
{ "created_at" : ISODate("2013-12-11T07:58:34.927Z"), "name" : "A" }

The expected result was [F,C,E] (i.e the first 3 entries from the second query-result). But instead I get [C,B,A], which is the first 3 entries from the first query-result (sorted by creation time).

So mongodb is performing the limit operation before it performs the sort operation. Is there any way to force it to sort before applying the limit ?

A similar question was asked here, but there was an issue in the query itself. Is this issue specific to '$near' queries ?

Community
  • 1
  • 1
Apollyon G10
  • 83
  • 6

1 Answers1

2

The geo-spatial query already sorts the documents from nearest to farthest. So, if you have an additional sort() and limit(), limit() is applied first since the results are already sorted by distance.

Although I think it doesn't make much sense to sort by "created_at" in a geo-spatial query, if you still need it, it can only be done programmatically. That is, sort the entire results in the query and apply the limit in your client program.

Anand Jayabalan
  • 12,294
  • 5
  • 41
  • 52
  • 1
    so would using a $geoWithin help ? – Apollyon G10 Mar 25 '14 at 12:14
  • [`$geoWithin`](http://docs.mongodb.org/manual/reference/operator/query/geoWithin/#op._S_geoWithin) is quite different from $near. They can't be used interchangeably. Their use cases are totally different. You can read about it in the documentation. I'd suggest to apply the limit in your client program for the use case you've described. – Anand Jayabalan Mar 25 '14 at 12:21
  • 1
    I realize that, but my use-case requires sorting ONLY by creation. The geo-spatial query just needs to ensure that the results lie within the maxDistance. So I can use the $geoWithin instead of $near here. Thanks, marking your answer as correct – Apollyon G10 Mar 25 '14 at 12:22