ruby-on-rails - Rails ActiveRecord 日期比较数据库不可知？

Question

我试图找到一种与数据库无关的方法来比较日期和事件记录查询。我有以下查询:

UserRole.where("(effective_end_date - effective_start_date) > ?", 900.seconds)

这在 MySQL 上运行良好，但在 PG 上产生错误，因为它生成的 sql 不包含“间隔”语法。从控制台:

  ←[1m←[36mUserRole Load (2.0ms)←[0m  ←[1mSELECT "user_roles".* FROM "user_roles" WHERE "user_roles"."effective_end_date" IS NULL AND ((effective_end_d
ate - effective_start_date) > '--- 900
...
')←[0m
ActiveRecord::StatementInvalid: PG::Error: ERROR:  invalid input syntax for type interval: "--- 900

当我使用 to_sql 选项运行它时，我得到:

irb(main):001:0> UserRole.where("effective_end_date - effective_start_date) > ?", 900.seconds).to_sql
=> "SELECT \"user_roles\".* FROM \"user_roles\"  WHERE \"user_roles\".\"effective_end_date\" IS NULL AND (effective_end_date - effective_start_date) >
'--- 900\n...\n')"

感谢所有帮助。

Answer 1

如果您的 effective_end_date 和 effective_start_date 列确实是日期，那么您的查询毫无意义，因为日期的最小分辨率为一天，而 900s 比 86400s 小很多(又名 25*60*60 或 1 天)。所以我假设你的“日期”列实际上是日期时间(又名时间戳)列；如果这是真的，那么您可能想要重命名这些列以避免在维护期间混淆，effectively_starts_at 和 effectively_ends_at 可能很适合通常的 Rails 约定。如果此假设无效，那么您应该更改列类型或停止使用 900s。

回到真正的问题。 ActiveRecord 使用 ActiveRecord::ConnectionAdapters::Quoting#quote 将 Ruby 值转换为 SQL 值方法:

def quote(value, column = nil)
  # records are quoted as their primary key
  return value.quoted_id if value.respond_to?(:quoted_id)

  case value
  #...
  else
    "'#{quote_string(YAML.dump(value))}'"
  end
end

因此，如果您尝试使用某些东西作为占位符的值，并且没有针对该类型的任何内置处理，那么您会得到 YAML(IMO 的一种奇怪的默认选择)。此外，900.seconds 是一个 ActiveSupport::Duration 对象(尽管 900.seconds.class 说的是什么)和 case 值 没有 ActiveSupport::Duration 的分支，因此 900.seconds 将获得 YAMLified。

PostgreSQL 适配器在 ActiveRecord::ConnectionAdapters::PostgreSQLAdapter#quote 中提供了自己的 quote但它也不知道 ActiveSupport::Duration。 MySQL 适配器的 quote也不知道 ActiveSupport::Duration。你可以给这些 quote 方法修补一些意义。在初始化程序中是这样的:

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
    # Grab an alias for the standard quote method
    alias :std_quote :quote
    # Bludgeon some sense into things
    def quote(value, column = nil)
        return "interval '#{value.to_i} seconds'" if(value.is_a?(ActiveSupport::Duration))
        std_quote(value, column)
    end
end

有了这个补丁，当你使用 ActiveSupport::Duration 时，你会得到 PostgreSQL 理解的时间间隔:

> Model.where('a - b > ?', 900.seconds).to_sql
 => "SELECT \"models\".* FROM \"models\"  WHERE (a - b > interval '900 seconds')" 
> Model.where('a - b > ?', 11.days).to_sql
 => "SELECT \"models\".* FROM \"models\"  WHERE (a - b > interval '950400 seconds')"

如果您将类似的补丁添加到 MySQL 适配器的 quote(留作读者练习)，则如下:

UserRole.where("(effective_end_date - effective_start_date) > ?", 900.seconds)

将在 PostgreSQL 和 MySQL 中做正确的事情，您的代码将不必为此担心。

---

也就是说，在不同的数据库上开发和部署是一个非常糟糕的主意，这会让圣诞老人哭泣并去寻找一些煤(可能含有砷，可能具有放射性)来装你的 socks 。所以不要那样做。

另一方面，如果您正在尝试构建与数据库无关的软件，那么您将度过一段快乐的时光!数据库可移植性在很大程度上是一个神话，与数据库无关的软件总是意味着在您的平台提供的 ORM 和数据库接口(interface)之上编写您自己的可移植性层。您将不得不在您计划支持的每个数据库上详尽地测试所有内容，每个人都口头上支持 SQL 标准，但似乎没有人完全支持它，每个人都有自己的扩展和需要担心的怪癖。您最终将编写自己的可移植层，该层将混合使用实用方法和猴子补丁。