I have a bunch of listings, I am trying to sort by featured and date in rails yet it's not working properly...

Question

Let's say I have a listing

a listing has a date and a featured (of true or false)

How do I order my listings to order by featured = true first?

Currently I have some listings as featured nil, then a few appeared as false - the ones that appeared as false are showing up before featured true. This makes sense because false is before true alphabetically (at leas this is what I'm assuming is the case)

If I'm typing

named_scope :sort_by_featured_date, :order => 'featured ASC, created_at DESC'

and if I do it desc then the featured = false will go on bottom but nil will go on top. Is the only proper way to force false somehow?

How do I get it to sort by featured = true as priority? I've tried variations in the order clause with no luck. Something about true being a missing field in itself.

Answer 1

You could COALESCE(featured, false) to ensure that if a row has 'featured=null', it will become 'false' and then get ordered correctly.

Answer 2

...
ORDER BY
    CASE
      WHEN featured = true THEN 0
      WHEN featured = false THEN 1
      WHEN featured IS NULL THEN 2
    END ASC,
    created_at DESC