How to sort by a calculated value in Activerecord?

Question

I have a rails API which returns JSON to my React front end. I'm trying to sort by a calculated value for each item in a collection. I have a Space model which has an area attribute and a count attribute. I'd like to sort the response by total_area which is just area * count. I'm able to do this using sort_by but the process is pretty slow even with less than 100 records:

@spaces = Space.all
@spaces = @spaces.sort_by(&:total_area) 

Where total_area is a Space class method:

def total_area
  self.area * self.count
end

Is there anyway to do this within the database to get an improvement in speed? I've tried using the order method:

@spaces.order( "count * area" => :asc)

But I get the following postgres error:

PG::UndefinedColumn: ERROR:  column spaces.count * area does not exist

Is is possible to do this in the database? Any suggestions on how I can, or how I could do this sort more quickly would be very much appreciated.

Answer 1

When you hand #order a hash:

@spaces.order("count * area" => :asc)

it assumes that the key is a column name so it sends SQL like this to the database:

order by "count * area" asc

Hence the PG::UndefinedColumn exception. BTW, double quotes are used in SQL to quote identifiers such as column and table names.

If you want to send an expression to the database as part of an ORDER BY clause then you want to pass that expression to #order as a string:

@spaces.order('count * area')
# If you want to be explicit about the sorting direction:
@spaces.order('count * area asc')
# If you might have other tables with those column names:
@spaces.order('spaces.count * spaces.area')

Note that newer versions of Rails will complain about:

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s)

when you attempt to pass a string to #order but you can get around that by adding an Arel.sql call:

@spaces.order(Arel.sql('count * area'))
@spaces.order(Arel.sql('count * area asc'))
@spaces.order(Arel.sql('spaces.count * spaces.area'))