Order by "title" but then put "Other" at the end?

rubyonrails-talk
1

Is there any way in a scope to order a list by a text field ("title", for instance) but put the item that is titled "Other" at the end of the list?

Thanks

2

Andy wrote in post #957534:

Is there any way in a scope to order a list by a text field ("title", for instance) but put the item that is titled "Other" at the end of the list?

The easiest way would involve using a separate DB field, so that you can do ORDER BY title, put_at_end.

Thanks

Best,

3

Is there any way in a scope to order a list by a text field ("title", for instance) but put the item that is titled "Other" at the end of the list?

Assuming that the item is always titled 'Other' and it will never be something else and you're okay with cheating a little bit this would work for Rails 3...

scope :almost_by_title, order("title = 'Other', title")

This gets messy pretty fast. For instance when you have to add 'Unknown' and 'Misc' to the end as well. But if it's all you need you can get away with it :slight_smile:

As Marnen said, setup another "sortable_title" field and order on that. The upside to doing this is you can also strip "A", "The", and "An" from the beginning of titles so they sort more naturally.

-philip

4

Philip Hallstrom wrote in post #957539:

Is there any way in a scope to order a list by a text field ("title", for instance) but put the item that is titled "Other" at the end of the list?

Assuming that the item is always titled 'Other' and it will never be something else and you're okay with cheating a little bit this would work for Rails 3...

scope :almost_by_title, order("title = 'Other', title")

Ooh, I like that.

This gets messy pretty fast. For instance when you have to add 'Unknown' and 'Misc' to the end as well. But if it's all you need you can get away with it :slight_smile:

As Marnen said, setup another "sortable_title" field and order on that.

That's not what I said. I was thinking of a Boolean put_at_end field.

The upside to doing this is you can also strip "A", "The", and "An" from the beginning of titles so they sort more naturally.

Excellent point.

-philip

Best,

5

Wouldn't that have to be ORDER BY put_at_end, title? (assuming put_at_end has a value of 0 or 1)

6

E. Litwin wrote in post #957563:

7

I solved this in an application by creating a separate "sort_name" field that was strictly used for sorting. Never seen or used by the user at all. We had a "before_save :build_sort_name" method that handled all sorts of cases. Wouldn't be too hard to add something similar for your scenario.

def build_sort_name   sort_name = name.gsub(/^(The )(.*)/, '\2, \1') # change "The Title" to "Title, The "   sort_name = "ZZZZZZZZZZZZZZ Other" if name = 'Other' # force 'Other' to sort after everything else end