Is there a way to put all the nil value at the end when sorting with mongodb/mongoid?

Question

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

Answer 1

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)

Answer 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.