when I do order on a counter_cache it returns nil objects first?

Question

I have owns_count counter_cache on my items model. When i do

Items.order("owns_cache DESC")

, it returns me objects that are nil before other results. If I do

"owns_cache ASC"

, it is correct.

What should I be doing?

Answer 1

How NULLs get ordered depends on the underlying database.

For PostgreSQL, you could do this:

Items.order("owns_cache DESC NULLS LAST")

For MySQL and SQLite:

Items.order("COALESCE(owns_cache, 0) DESC")

I think MySQL sorts NULLs at the bottom of a DESC ordering though so you might not need anything special there. This COALESCE approach will also work in PostgreSQL so this would be a portable solution that should give you consistent results everywhere.

If you wanted NULLs at the bottom on an ASC sort, you'd replace the 0 with something larger than the largest owns_cache number.