活动记录查询和heroku问题。 [英] Issue with active record querying and heroku.

问题描述

我只是试图运行查询以查找数据库中 datetime列中的值小于当前unix时间戳记的所有记录。

I'm simply trying to run a query to find all the records in a database that have a value in a "datetime" column that's less than the current unix timestamp.

当前，这是我的代码。它在本地运行良好。

Currently this is my code. It runs fine locally.

t = Time.new.to_i
Event.where("datetime < #{t}")

当我在heroku控制台中对此进行测试时，会出现此错误。

When I test this in the heroku console I get this error.

>> Event.where("datetime < #{t}")
ActiveRecord::StatementInvalid: PGError: ERROR:  operator does not exist: character varying < integer
LINE 1: SELECT "events".* FROM "events"  WHERE (datetime < 132462148...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "events".* FROM "events"  WHERE (datetime < 1324621488)
    /app/.bundle/gems/ruby/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:1003:in `async_exec'
    /app/.bundle/gems/ruby/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:1003:in `exec_no_cache'
    /app/.bundle/gems/ruby/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:591:in `block in exec_query'
    /app/.bundle/gems/ruby/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:244:in `block in log'

有什么想法吗？

推荐答案

您应该使用占位符来获取正确的格式并确保它是正确引用：

You should be using a placeholder to get the right format and ensure that it is properly quoted:

t      = Time.new
events = Event.where("datetime < :t", :t => t)

您不能比较 timestamp 列在PostgreSQL中带有一个整数，但是您可以在SQLite中使用。您必须将 timestamp 与另一个 timestamp比较（或 date ）或可以解析为 timestamp 的字符串。将不起作用：

You can't compare a timestamp column with an integer in PostgreSQL but you can in SQLite. You have to compare your timestamp with another timestamp (or date) or a string that can be parsed as a timestamp. This SQL won't work:

SELECT "events".* FROM "events" WHERE (datetime < 132462148)

但是这些将：

SELECT "events".* FROM "events" WHERE (datetime < '2011-12-23 06:52:25.096869')
SELECT "events".* FROM "events" WHERE (datetime < '2011-12-23')

这里有几节课：

1. 如果要部署到Heroku，还应该在PostgreSQL之上进行开发，ActiveRecord不会使您摆脱各种数据库之间的所有差异。 / li>
   
2. 您应该让ActiveRecord担心类型转换会尽可能地产生问题，如果您要与日期或时间进行比较，请使用占位符并将AR传递给某种时间对象，然后让AR担心。


3. 尽可能使用占位符而不是字符串插值。

这篇关于活动记录查询和heroku问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！