by Matt Polito on February 24, 2014

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][order api].

Motel.order('name asc')

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

![mind blown][]

I completely missed this wonderful update. Looks like it was [introduced][change commit] 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.

[order api]: http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-order [change commit]: https://github.com/rails/rails/commit/633ea6a826cb5e587165f78d15770271cb39f670 [mind blown]: http://img.photobucket.com/albums/v201/foolishtook/HIMYM/mindblown.gif