sort_by => :sql in and polymorphic associations

Assume you have a polymorphic association in Rails.

class MyObject < ActiveRecord::Base 
  belongs_to :another_object, :polymorphic => true
end

Well, "another object" can now be in any other table of the DB, and in the my_objects table there are two columns (assuming you followed the conventions) named another_object_id and another_object_type, the first one containing the ID of the object, the second the model name.

Now let's say I have a table with all "my objects" and I want to sort it according to a specific column in "another object" (for simplicity let us assume any possible "other object" have a column called "label").

There is two ways to do this. The first one is loading all "my objects" instances, then for each one make a query, according to the association type, to find the other objects, than let Ruby sort by label. This is of course not optimized. Through the
'eager loading' ActiveRecord feature, which I think is in the meantime also available for polymorphic associations, it is probably possible to find a better way. However I wanted a single query, so I did it using SQL; case was introduced in the standard, if I am not wrong, in the SQL-92 version. I think that most SQL-DBs comply, SQL-Lite probably excluded (I tested only on Oracle).

So in my case another_objects can only be of a few types, so I did it this way, in the controller code:

#
# e.g. types = %w[Cat Dog Mouse]
#
def sort_by_sql(types)
  sql = '(case (another_object_type)'
  sql << types.map do |type|
           'when #{type} 
            then (select label 
                 from #{type.tableize} t
                 where t.id = another_object_id) '
         end.join('')
  sql << 'end)'
end
private :sort_by_sql

You can use this sort_by_sql() method in the :order_by => sort_by_sql(...) key of the find method to sort by the label method of the polymorphic association with only one query. This was sensibly faster in my case.

Actually the need for this came because I wanted to sort by a polymorphic association in an ActiveScaffold based controller, and in this case I am not sure I could have specified an eager loading without too much effort (probably overriding the finder method of the list). So I just wrote the sort_by_sql() as a class method and used it in the config block of my active scaffold:

AllowedTypes = %w[Cat Dog Mouse]
def self.sort_by_sql(types)
  #...the code up here...#
end
active_scaffold :my_objects do |config|
  # ...
  config.columns[:another_object].sort_by :sql => 
                           sort_by_sql(*AllowedTypes)
  # ...
end

That worked fine for me.