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

问题描述

我有一个具有日期属性的 ActiveRecord 模型.是否可以利用该日期属性按年、日和月查找:

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)

或者可以简单地找到_by_date(2012-12-1).

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

我希望我可以避免创建年、月和日属性.

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，因为它不知道 extract 是什么:

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屋！