Rails: Order by sum of two columns

Question

So, I have a Photo model which can be downloaded at full_size and presentation_size. When a user downloads a photo I track this on the photo's full_downloads and presentation_downloads attribute.

That's all good.

Sometimes I want to know how many total downloads there have been. I have a simple method, total_downloads which looks like so:

def total_downloads
  self.full_downloads + self.presentation_downloads
end

My question is: I would like to be able to order photos by all three of these (full, presentation, total downloads). The first two are easy, but how do you do an order by the sum of two columns? Note this needs to be both SQLite and PG compatible at minimum.

A side question, would it be faster to make the total_downloads method a query, and if so what's the best way to write that? I know to sum on the class you can call Photo.sum(...), but I'm not sure how to do that for two columns on a single record.

Thanks!

Answer 1

You can try this:

Photo.order('full_downloads + presentation_downloads')

It will run this SQL query:

SELECT "photos".* FROM "photos" ORDER BY full_downloads + presentation_downloads

This is potentially slow though. If you have a large dataset and use this sort order often, you should consider creating a total_downloads column and recalculating its value if the record's full_downloads or presentation_downloads column changes.

Answer 2

Photo.order('full_downloads + presentation_downloads DESC')

That would definitely be much faster than doing the sort in Ruby.

Answer 3

You now might have to add Photo.order(Arel.sql('full_downloads + presentation_downloads')) to avoid UnknownAttributeReference error