Sphinx ordering: nil/NULL values last

Question

On a Rails project, I'm using Sphinx together with Thinking Sphinx plugin. I index a table with an attribute :foo that is a float.

My desired behaviour when sorting for column :foo would be that nil values always appear at the end of the list, e.g.

id; foo (order foo desc)
-------
1; 5
2; 3
3; -4
4: -5
5: nil
6: nil


id; foo (order foo asc)
-------
4: -5
3; -4
2; 3
1; 5
5: nil
6: nil

If it was normal sql, I'd sort like:

:order => "(foo IS NULL) ASC, foo DESC"

But it seems not to be possible, as I think NULL values are translated to 0 (is that true?). Using sphinx ordering expressions seems not to sort my floats properly.

Did anybody solve this problem or has an idea on how to do it?

Answer 1

One solution I came up with in the meantime is to index an extra attribute, like this:

define_index do 
  indexes foo, :sortable => true
  has "foo IS NULL", :as => :foo_nil, :sortable => true
end

what lets me order like this

:order => "foo_nil ASC, foo DESC"

It's a bit clumsy, especially as I have many attributes that I want to have ordered like this.

Answer 2

The solution you've provided is what I would suggest - as yes, you're correct, Sphinx treats NULLs as 0's.