6

Really not much more to tell than what is already in the question.

using mongoid: 

People.asc(:age)

I get nil value first.
is there a way to always return nil last, or to tell mongodb to treat nil as very high?

Exactly like what is answered for the same problem in sql here

Community
  • 1
  • 1
Stéphane
  • 11,755
  • 7
  • 49
  • 63

2 Answers2

3

Not if you want ascending order for the non-nil ages. If that is unimportant, you could see if

People.desc(:age)

will put the nil values at the end. Alternatively, you could try adding a search parameter to not return nil values:

People.not_in(age: [nil]).asc(:age)
XanderStrike
  • 726
  • 9
  • 20
mattbornski
  • 11,895
  • 4
  • 31
  • 25
2

I'm pretty sure the answer is "no" for MongoDB. There's no way to supply a custom sorting function, you can only supply the keys to sort on. There is a request for custom sorting functions and they even mention your particular use case:

javascript sort helper for custom sorting / indexing
[...]
Could this be used to sort alphabetically on a field, and put documents with a null value at the end of the result set?
[...]
@nick - yes

So you're not alone in wanting to put nulls at one end or the other.

I think the best you can do right now is to do it in Ruby, something like this:

nils, not_nils = People.asc(:age).partition { |p| p.age.nil? }
people = not_nils + nils

I don't use Mongoid but presumably asc gives you an Enumerable, if not then perhaps you could stick a to_a in there. Of course this sort of hackery is useless if you're paginating.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Thanks for the link to the requested feature. I will follow this. Mongoid uses criterias, so I will need a to_a in there. But I think we can do without the nil ones after all. And as you say, paging will make this much less important. – Stéphane Feb 08 '12 at 23:05
  • after looking at it closer, What is proposed there is javascript sorting based on a function. this would mean that to sort the queried collection, mongodb will have to iterate over the whole collection, which is certainly not solving the problem. What I wish mongodb had is much simpler : null last, or null first. – Stéphane Feb 14 '12 at 08:27
  • @Stephane: sorting requires the database to spin through the whole result set applying a unction anyway, the problem is that MongoDB only allows you to use `a > b` or `a < b` as your comparison function. – mu is too short Feb 14 '12 at 17:38
  • @mu_is_too_short i was assuming that there were optimization in the DB if you have indexes on what you sort. – Stéphane Feb 15 '12 at 10:00
  • An index should help you look at the whole collection but you still have to look st the whole thing. The fundamental problem is that it compares things the way JavaScript does and you can't do anything about that. – mu is too short Feb 15 '12 at 10:11
  • partition results in array and we can't further drill down the result in the same way as we do with Mongoid::Criteria. How do we achieve that with mongoid? – Shrikanth Hathwar May 15 '18 at 12:20
  • @ShrikanthHathwar Last time I looked, you can't. I moved away from MongoDB a couple years ago. – mu is too short May 15 '18 at 17:30