Find all rows ordered by method in ActiveRecord

Question

I have an ActiveRecord model with a field called name (in the database) and a method called collation_name (not in the database) that returns a reduced version of the name. The model class resembles this:

class Foo < ActiveRecord::Base

  # ... other stuff ...

  def collation_name
    words = name.downcase.split
    if words[0] == 'the'
      words.shift
    end
    words.join(' ')
  end
end

My application is used to export the application database to a second database containing a denormalized version of the data, including collation_name as a real column. I'd like to use collation_name to order records for display within the application's views. I tried Foo.all(:order=>:collation_name) but got a 'no such column: collation_name' error.

How do I get a list of Foo records ordered by collation_name? Do I have to add collation_name as a real column in the application database for ActiveRecord to see it when ordering? Should I sort in my application code after ActiveRecord has returned results?

Answer 1

In order for :order to work you have to use a column that is known in the query that is created by the .all method, or you have to overload the .all method in your class to add the missing functionality.

From your question, I am not sure how your database looks like, hence it is not discernible what column you could use for :order. You may have to order the resulting array of the .all method with ruby if the order you expected cannot be reached by the sql query built by rails.

Answer 2

If you only have to deal with a limited amount of rows sort inside your application like this:

class Foo

  attr_accessor :name

  def initialize(name)
    self.name = name
  end

  def collation_name 
    name
  end

  def <=>(other)
    collation_name <=> other.collation_name
  end

end

# create 10 foos
foos = Array.new(10).map { |e| Foo.new(rand(100_000).to_s) }
p foos # foos are unsorted
p foos.sort # foos are sorted 

# alternative: use a closure to sort
Foo.send :remove_method, '<=>'.to_sym
p foos.sort { |a,b| a.collation_name <=> b.collation_name } # sorted again

If you want to order the result sets using ActiveRecord you need to have the column present in the database. Either

• use a function which derives the result for you (essentially the same code you are using in Ruby) and make it part of the result set (e.g. by putting it in a view and query the view using :from)
• compute the data and store it in the database (denormalize it, like you said)