ActiveRecord在日期字段上按年，日或月查找 [英] ActiveRecord Find By Year, Day or Month on a Date field

问题描述

我有一个具有日期属性的ActiveRecord模型。是否可以利用该date属性按年，日和月查找：

I have an ActiveRecord model that has a date attribute. Is it possible to utilize that date attribute to find by Year, Day and Month:

Model.find_by_year(2012)
Model.find_by_month(12)
Model.find_by_day(1)

可能会找到find_by_date（2012-12-1）。

or is it simply possible to find_by_date(2012-12-1).

我希望可以避免创建Year，Month和Day属性。

I was hoping I could avoid creating Year, Month and Day attributes.

推荐答案

假定您的日期属性是日期（而不是完整的时间戳），则简单的 where 会给您按日期查找：

Assuming that your "date attribute" is a date (rather than a full timestamp) then a simple where will give you your "find by date":

Model.where(:date_column => date)

您不希望 find_by_date_column 这样最多可以得到一个结果。

You don't want find_by_date_column as that will give at most one result.

对于年，月和日查询，您想使用 extract SQL函数：

For the year, month, and day queries you'd want to use the extract SQL function:

Model.where('extract(year  from date_column) = ?', desired_year)
Model.where('extract(month from date_column) = ?', desired_month)
Model.where('extract(day   from date_column) = ?', desired_day_of_month)

但是，如果您使用的是SQLite，则必须弄乱 strftime 因为它不知道什么提取是：

However, if you're using SQLite, you'd have to mess around with strftime since it doesn't know what extract is:

Model.where("cast(strftime('%Y', date_column) as int) = ?", desired_year)
Model.where("cast(strftime('%m', date_column) as int) = ?", desired_month)
Model.where("cast(strftime('%d', date_column) as int) = ?", desired_day_of_month)

％m 和％d 格式说明符在某些情况下会添加前导零，这可能会使相等性测试混淆，因此 cast（... as int）强制将格式化的字符串转换为数字。

The %m and %d format specifiers will add leading zeroes in some case and that can confuse the equality tests, hence the cast(... as int) to force the formatted strings to numbers.

ActiveRecord不会保护您不受数据库之间所有差异的影响，因此请您尽快执行任何不平凡的事情，要么要么构建自己的可移植性层（丑陋但有时是必要的），要么将自己绑定到一组有限的数据库上（除非要发布必须在任何数据库上运行的东西，否则这是现实的），或者完成所有工作Ruby中的逻辑（对于任何不重要的数据量来说都是疯狂的）。

ActiveRecord won't protect you from all the differences between databases so as soon as you do anything non-trivial, you either have to build your own portability layer (ugly but sometimes necessary), tie yourself to a limited set of databases (realistic unless you're releasing something that has to run on any database), or do all your logic in Ruby (insane for any non-trivial amount of data).

在大型表上，年，月和日的查询将非常缓慢。有些数据库允许您在函数结果上添加索引，但是ActiveRecord太愚蠢而无法理解它们，因此，如果尝试使用它们，将会造成很大的麻烦。因此，如果您发现这些查询太慢，则必须添加要避免的三个额外列。

The year, month, and day-of-month queries will be pretty slow on large tables. Some databases let you add indexes on function results but ActiveRecord is too stupid to understand them so it will make a big mess if you try to use them; so, if you find that these queries are too slow then you'll have to add the three extra columns that you're trying to avoid.

如果要要大量使用这些查询，则可以为其添加范围，但推荐的方法是添加带有参数的作用域只是添加一个类方法：

If you're going to be using these queries a lot then you could add scopes for them but the recommended way to add a scope with an argument is just to add a class method:

使用类方法是首选的接受方法范围的参数。这些方法仍然可以在关联对象上访问...

Using a class method is the preferred way to accept arguments for scopes. These methods will still be accessible on the association objects...

因此，您将拥有如下所示的类方法：

So you'd have class methods that look like this:

def self.by_year(year)
    where('extract(year from date_column) = ?', year)
end
# etc.

这篇关于ActiveRecord在日期字段上按年，日或月查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！