Heroku 的 Rails 数据库特定查询

Question

我最近一直在 Heroku 上部署一些应用程序。我在本地开发机器上运行 MySQL，并花了一些时间更新一些范围以在 PostgreSQL 中工作。然而，我收到的一个错误被证明很难改变。

目前我的模型中有一个数据库特定的案例语句。我理解为什么会发生有关 MySQL 日期函数的错误，但我不确定这是否是最有效的解决方案。有谁有更好的方法来实现适用于 MySQL 和 PostgreSQL 的修复程序？

  case ActiveRecord::Base.connection.adapter_name
  when 'PostgreSQL'
    named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (date_part('year', created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_month, lambda { |*args| {:conditions => ["published = ? AND (date_part('month', created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_day, lambda { |*args| {:conditions => ["published = ? AND (date_part('day', created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
  else
    named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (YEAR(created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_month, lambda { |*args| {:conditions => ["published = ? AND (MONTH(created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
    named_scope :by_day, lambda { |*args| {:conditions => ["published = ? AND (DAY(created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }
  end

仅供参考，这是我收到的 PostgreSQL 错误：

PGError: ERROR: function month(timestamp without time zone) does not exist LINE 1: ...T * FROM "articles" WHERE (((published = 't' AND (MONTH(crea... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. : SELECT * FROM "articles" WHERE (((published = 't' AND (MONTH(created_at) = '11')) AND (published = 't' AND (YEAR(created_at) = '2010'))) AND ("articles"."published" = 't')) ORDER BY created_at DESC LIMIT 5 OFFSET 0

提前感谢任何人提供的任何信息。

Answer 1

您应该使用标准 EXTRACT 函数：

named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (extract(year from created_at) = ?)", true, (args.first)], :order => "created_at DESC"} }

两者 PostgresSQL 和 MySQL 支持它。

Answer 2

不幸的是，这种情况经常发生，但你的想法总体是正确的。

第一种攻击方法是查看 MySQL 和 Postres 中是否存在同时存在的函数，但在本例中这是不可能的。

我要提出的一个建议是，这个解决方案中有很多代码重复。考虑到条件语句是这里唯一的兼容问题，我将仅针对条件排除兼容性检查：

示例（半伪代码）：

named_scope :by_year, lambda { |*args| {:conditions => ["published = ? AND (#{by_year_condition} = ?)", true, (args.first)], :order => "created_at DESC"} }


#...code...

def by_year_condition
  if postgres
     "date_part('year', created_at)"
  else
     "YEAR(created_at)"
end

Answer 3

另一种选择是为每个日期部分（日、月 和年）创建计算列，并直接针对这些部分进行查询。您可以让他们使用您的模型代码或触发器保持最新状态。您还可以受益于能够对年、月和日列上的各种组合建立索引。当您在 where 子句中使用函数时，尤其是当该函数从列中间提取部分数据时，数据库在正确使用索引方面是出了名的糟糕。

拥有三个独立列的好处是您的查询将不再依赖于任何供应商的 SQL 实现。