Ruby-on-rails 我该怎么做呢？Model.where("created_at >= #{Time.now - 5.days}")

How do I do this? Model.where("created_at >= #{Time.now - 5.days}")

提问by thewillcole

This has been bugging me for a while...

这已经困扰我一段时间了......

How can I string interpolatea datetimein Rails ActiveRecord queries?

我怎样才能串插值一个datetime在Rails的ActiveRecord的查询？

# Works, but supeh ugleh:
Model.where("created_at >= ?", Time.now - 5.days)

# How do I do this?
Model.where("created_at >= #{Time.now - 5.days}")
# As is, it produces the following error message:
# ActiveRecord::StatementInvalid: PG::Error: ERROR:  syntax error at or near ...

The reason I care is for code readability:

我关心的原因是代码可读性：

# I like this better:
Model.where("created_at >= #{Time.now - 5.days} OR" + \
            "updated_at >= #{Time.now - 3.days}")

# than this:
Model.where("created_at >= ? OR updated_at >= ?", Time.now - 5.days, Time.now - 3.days)

回答by mu is too short

I'd advise against using string interpolation for this, there are many sharp edges and you'll probably have more fun bobbing for apples in a bucket of fish hooks. You should do it this way:

我建议不要为此使用字符串插值，有很多锋利的边缘，你可能会在一桶鱼钩中钓苹果更有趣。你应该这样做：

Model.where(
    'created_at >= :five_days_ago or updated_at >= :three_days_ago',
    :five_days_ago  => Time.now - 5.days,
    :three_days_ago => Time.now - 3.days
)

Using (well) named placeholders gives you the readability and position independence that you think string interpolation offers but nicely sidesteps the quoting, timezone, and format issues that string interpolation forces on you.

使用（良好）命名的占位符可为您提供您认为字符串插值提供的可读性和位置独立性，但很好地避开了字符串插值强加给您的引用、时区和格式问题。

But how do you safely use string interpolation? There are a few things you must handle yourself:

但是如何安全地使用字符串插值呢？有几件事你必须自己处理：

1. Quoting and escaping.
2. Timestamp formats.
3. Maybe timezones too.

1. 引用和逃避。
2. 时间戳格式。
3. 也许时区也是。

ActiveRecord will take care of all this nonsense for you.

ActiveRecord 将为您处理所有这些废话。

Don't try to do the quoting yourself, use the driver's quoting methods. You will have access to connection.quotefor properly quoting strings.

不要试图自己做引用，使用驱动程序的引用方法。您将有权访问以connection.quote正确引用字符串。

Any database will know what to do with ISO 8601 timestampsand there is a convenient iso8601method for that. ISO 8601 also conveniently includes the timezone and the database should be able to parse that (but if it can't then you'll have to convert your times to UTC by hand with .utc).

任何数据库都知道如何处理ISO 8601 时间戳，并且有一种方便的iso8601方法。ISO 8601 还方便地包含时区，数据库应该能够解析它（但如果不能，则您必须手动将时间转换为 UTC .utc）。

So, to be safe:

所以，为了安全起见：

Model.where("created_at >= #{connection.quote((Time.now - 5.days).utc.iso8601)} " + \
         "OR updated_at >= #{connection.quote((Time.now - 3.days).utc.iso8601)}")

Not so pretty now is it? With ISO 8601 timestamps you should be safe replacing the connection.quotecalls with simple single quotes:

现在没那么漂亮了吧？使用 ISO 8601 时间戳，您应该可以安全地用connection.quote简单的单引号替换调用：

Model.where("created_at >= '#{(Time.now - 5.days).utc.iso8601}' " + \
         "OR updated_at >= '#{(Time.now - 3.days).utc.iso8601}'")

but you still have a lot of noise and ugliness and you'll be developing bad habits.

但是你仍然有很多噪音和丑陋，你会养成坏习惯。

We're not partying like PHP programmers in 1999 so don't give in to false laziness by using string interpolation in your SQL, use named placeholders.

我们不像 1999 年的 PHP 程序员那样聚会，所以不要通过在 SQL 中使用字符串插值而屈服于虚假的懒惰，使用命名占位符。

回答by Mike Campbell

Old question, but my favoured method is:

老问题，但我最喜欢的方法是：

Model.where(created_at: 5.days.ago..Time.current)

Much prettier and more readable.

更漂亮，更具可读性。

Also, Rails 3.2 introducedsome Active Support helper methods to get some common ranges, Time#all_day, Time#all_week, Time#all_quarterand Time#all_year, so you could for instance do:

此外，Rails 3.2 引入了一些 Active Support 辅助方法来获取一些常见范围Time#all_day、Time#all_week、Time#all_quarter和Time#all_year，因此您可以例如执行以下操作：

Model.where(created_at: Time.current.all_week)