Rails Quick Tips - ActiveRecord Ordering
Turns out that using strings in your ActiveRecord query could be hurting you. Find out more at 11... or now, whatever's better for you.
Ordering via ActiveRecord is nothing new. You may be asking yourself, what could you possibly be informing us of and more importantly why is it hurting me???
For brevity, let's review.
ActiveRecord allows us to pass order with some options to build the ordering portion of a query.
Although you can pass a symbol or a string column name, I'm going to go out on a limb and say the best way is to provide it as a symbol.
Why would I say this ? Let me ask you... Do you pay attention to the queries that get produced?
When passing the a string column name:
Motel.order('name asc').to_sql => SELECT "motels".* FROM "motels" ORDER BY name asc
Now when passing a symbol column name:
Motel.order(:name).to_sql => SELECT "motels".* FROM "motels" ORDER BY "motels"."name" ASC
See the difference? When the symbol is passed, the query is built using fully qualified and properly quoted column names. Most of the time your query will run fine but this does matter! The issue comes up when you start joining tables or merging queries. Without the column being fully qualified with the table name, your query may produce an ambiguous match. This occurs when your joined tables have columns with the same name.
Lesson learned: use symbols when ordering
This is all fine and dandy for queries that are running in an ascending order as this is the default, but how do you descend? Normally I would jump down into [Arel][arel] to gain a bit more power. This allows us to build a descending query like so
Motel.order(Motel.arel_table[:name].desc).to_sql => SELECT "motels".* FROM "motels" ORDER BY "motels"."name" DESC
Hey, that does the job... everything is properly qualified and quoted. Life is grand. Until you look back at your code that is. It's not terrible but it is quite verbose. Turns out ActiveRecord order has some extra power built in. Did you know you can pass a column name to order as a key with the direction as a value?
Motel.order(name: :desc).to_sql => SELECT "motels".* FROM "motels" ORDER BY "motels"."name" DESC
I completely missed this wonderful update. Looks like it was introduced around ActiveRecord 4.0.0.
Now it is super easy to create a descending order query that is properly quoted and qualified. Of course you can call ascending to be explicit but remember that passing the symbol alone will do this for you.