ActiveRecord 聚合函数:是否有与数据库无关的 'EXTRACT(WEEK from date)'? [英] ActiveRecord aggregate function: is there a database-agnostic 'EXTRACT(WEEK from date)'?

问题描述

我有一个很好的方法(使用 rails 3 和 PostgreSQL)；我只是想知道是否有可能使它与数据库无关:

<前>FarmGatePrice.average :price, :group => 'EXTRACT(WEEK FROM date)'

据我所知，从日期中提取一周 ISO 编号的方法总是特定于数据库的.

我可以使用类似虚拟属性的东西 :week 所见 here，但似乎 :group 选项只接受原始 SQL.我也看过这个解决方案，但它不符合我的需求.另一种方法是使用数据库视图，甚至添加一个由回调方法填充的 :week 列 - 但我不想弄乱我的架构.

那么，有什么线索吗?

解决方案

不 - 如果您需要按周进行分组，最好通过 将其作为额外列集直接保存在数据库中before_save 回调.

正如您所说，从日期中提取一周是非常重要的，因此最好让 Rails 来处理它而不是您的数据库.这也将提高性能.

更新:

示例回调:

def 东西before_save :设置周私人的def set_weekself.week = self.date.cweek结尾结尾

i have a method that works just fine (with rails 3 and PostgreSQL) ; i just wonder if it's possible to make it database-agnostic :

FarmGatePrice.average :price, :group => 'EXTRACT(WEEK FROM date)'

As i understand it, methods to extract a week ISO number from a date are always database-specific.

I could use something like a virtual attribute :week as seen here, but it seems the :group options only accepts raw SQL. I've also seen this solution but it doesn't fit my needs. Another way would be to use a database view, or even to add a :week column filled by a callback method - but i'd prefer not to mess with my schema.

So, any clue ?

解决方案

Nope - if you need to do a group by week, you're better off saving it directly in the database as an extra column set via a before_save callback.

As you say, extracting a week from a date is non-trivial, and so it's best to let Rails handle this rather than your database. This will improve performance too.

Update:

Example callback:

def Thing

  before_save :set week

  private
     def set_week
        self.week = self.date.cweek
     end
end

这篇关于ActiveRecord 聚合函数:是否有与数据库无关的 &amp;#39;EXTRACT(WEEK from date)&amp;#39;?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！