Rails：按两列的总和排序

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.

所以,我有一个Photo模型,可以在full_size和presentation_size下载。当用户下载照片我照片的full_downloads和presentation_downloads属性跟踪此。

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:

有时我想知道有多少下载量。我有一个简单的方法,total_downloads看起来像这样:

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.

我的问题是:我希望能够通过所有这三个(完整,演示,总下载)订购照片。前两个很容易,但是你如何通过两列的总和来做订单呢?请注意,这至少需要SQLite和PG兼容。

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.

一个附带问题,将total_downloads方法作为查询会更快吗?如果是这样,最好的方法是什么呢?我知道总结你可以调用Photo.sum(...)的类,但我不知道如何在一条记录上为两列做到这一点。

Thanks!

2 个解决方案

#1

You can try this:

你可以试试这个:

Photo.order('full_downloads + presentation_downloads')

It will run this SQL query:

它将运行此SQL查询:

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.

但这可能很慢。如果您有一个大型数据集并经常使用此排序顺序,则应考虑创建total_downloads列并在记录的full_downloads或presentation_downloads列更改时重新计算其值。

#2

Photo.order('full_downloads + presentation_downloads DESC')

Photo.order('full_downloads + presentation_downloads DESC')

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

这肯定比在Ruby中进行排序要快得多。

#1