Search code examples
ruby-on-railscounter-cache

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

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?

Solution

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