Rails: ActiveRecord db sort operation case insensitive

Question

I am trying to learn rails [by following the SAAS course in coursera] and working with simple Movie table using ActiveRecord.

I want to display all movies with title sorted. I would like it to be sorted case insensitively.

I tried doing it this way:

Movie.all(:conditions => ["lower(title) = ?", title.downcase],:order => "title DESC")
=>undefined local variable or method `title' for #<MoviesController:0xb4da9a8>

I think it doesnt recognise lower(title) .

Is this the best way to achieve case insesisitve sort ?

Thanks!

score 23 · Accepted Answer · answered Aug 04 '12 at 04:57

23

Use where and not all

Movie.where("lower(title) = ?", title.downcase).order("title DESC")

Don't really understand the sort though. Here you'll get all movies with lower title equalling to title.downcase. Everything is equal, how could you sort it by title desc ?

To sort reverse-alphabetically all movies by lowercase title :

Movie.order("lower(title) DESC").all

answered Aug 04 '12 at 04:57

Anthony Alberto

10,325
3
34
38

2

In Rails 6 this won't be allowed anymore, make sure to wrap any SQL like these in Arel.sql() (https://github.com/rails/rails/commit/310c3a8f2d043f3d00d3f703052a1e160430a2c2) – Nicolas Connault Mar 02 '19 at 02:37

score 9 · Answer 2 · answered Aug 04 '12 at 05:01

9

You have to do this:

Movie.order("lower(title) DESC").all

answered Aug 04 '12 at 05:01

Dogbert

212,659
41
396
397

score 6 · Answer 3 · answered Oct 20 '17 at 00:58

A more robust solution is to use arel nodes. I'd recommend defining a couple scopes on the Movie model:

scope :order_by_title, -> {
  order(arel_table['title'].lower.desc)
}

scope :for_title, (title)-> {
  where(arel_table['title'].lower.eq title.downcase)
}

and then call Movie.for_title(title).order_by_title

The advantage over other answers listed is that .for_title and .order_by_title won't break if you alias the title column or join to another table with a title column, and they are sql escaped.

Like rickypai mentioned, if you don't have an index on the column, the database will be slow. However, it's bad (normal) form to copy your data and apply a transform to another column, because then one column can become out of sync with the other. Unfortunately, earlier versions of mysql didn't allow for many alternatives other than triggers. After 5.7.5 you can use virtual generated columns to do this. Then in case insensitive cases you just use the generated column (which actually makes the ruby more straight forward).

Postgres has a bit more flexibility in this regard, and will let you make indexes on functions without having to reference a special column, or you can make the column a case insensitive column.

Thanks for this. An underrated answer, in my opinion. – Michael Evans Jan 16 '20 at 15:17 — Michael Evans, Jan 16 '20 at 15:17

rickypai · Answer 4 · 2012-08-04T05:06:42.817

3

Having MySQL perform upper or lower case operation each time is quite expensive.

What I recommend is having a title column and a title_lower column. This way, you can easily display and sort with case insensitivity on the title_lower column without having MySQL perform upper or lower each time you sort.

Remember to index both or at least title_lower.

edited Aug 04 '12 at 05:06

answered Aug 04 '12 at 04:58

rickypai

4,016
6
26
30

Rails: ActiveRecord db sort operation case insensitive

4 Answers4

Linked