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.

mu is too short · Accepted Answer · 2018-08-17T19:36:55.470

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'))

it's been years since its answered, any updates for this or using `SQL query` is the only way? — buncis, Jan 24 '22 at 16:03
@buncis AFAIK SQL is still the easiest way, you'll want to wrap it in `Arel.sql(...)` these days though. You could do something with a bunch of AREL calls but that would IMHO be harder to read. — mu is too short, Jan 24 '22 at 18:22
yeah I've read the discussions on rails forum though https://discuss.rubyonrails.org/t/what-has-happened-to-arel/74383 they consider arel as private API so `SQL query` is the recommended way — buncis, Jan 25 '22 at 02:19

buncis · Answer 2 · 2022-01-25T10:21:31.323

consider to use virtual/generated column for solving this problem

instead having method and calculation in ruby like this

def total_area
  self.area * self.count
end

we could use generated/virtual column from mysql/postgresql

# db/migrate/20131220144913_create_spaces.rb
create_table :spaces do |t|
  t.decimal :area
  t.integer :count
  t.virtual :total_area, type: :decimal, as: 'area * count', stored: true
end

# app/models/spaces.rb
class Space < ApplicationRecord
end

# Usage
Space.create(area: 15.5, count: 2)
Space.last.total_area # => 31
Space.all.order(total_area: :asc)

related docs pg12 mysql AR psql

the requirements for this are mysql 5.7 and rails 5 or postgresql 12 and rails 7

How to sort by a calculated value in Activerecord?

2 Answers2