将Postgres函数用作ActiveRecord模型

Question

我创建了一个Postgres函数，用于执行涉及多个表连接的复杂查询，所有这些表都必须由动态日期字段进行过滤。

该函数运行完美，使我能够执行像"SELECT * FROM trail_for_date('2014-01-01')"这样的查询，并返回一个表。

以下是Postgres文档中关于函数的简化示例：

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

我该如何将这个返回表格作为Rails/Ruby模型使用，并使函数的参数具有动态性？

类似于以下代码（显然不起作用）：

class SimplifiedExample < ActiveRecord::Base
  self.table_name = 'sum_n_product_with_tab(:dynamic_input)'
end

- Helios de Guerra

---

这是我最初走的路线，但在我的查询中，我必须连接大约五个表，这些表都过滤单个日期并基于该日期进行计算...因此，我需要能够传递某种变量或使用函数来实现这一点（我想）。 - Helios de Guerra

我想我可以，尽管这是一个超过100行的查询，而且我必须保持我的查询与数据库人员实现的数据库函数同步。 - Helios de Guerra

或者您可以使用arel Model.select(Arel::Nodes::NamedFunction.new('sum_n_product_with_tab', 6)).to_sql。 - Andy Henson

Answer 1

创建一个虚拟表，其列与您的函数输出相同。

CREATE TABLE report.compliance_year (
year TIMESTAMP,
compliance NUMERIC(20,2),
fund_id INT);

创建您的模型：

class Visualization::ComplianceByYear < ActiveRecord::Base
    self.table_name = 'report.compliance_year'
    def compliance_by_year(fund_id)
        Visualization::ComplianceByYear.find_by_sql(["
            SELECT year, compliance, fund_id
              FROM report.usp_compliance_year(ARRAY[?])", fund_id])
    end
end 

在您的控制器中引用它，并使用函数调用结果填充它：

def visualizations
  @compliancebyyear = Visualization::ComplianceByYear.new()
  @compliancefunds = @compliancebyyear.compliance_by_year(current_group.id)
end

- Caullyn

Answer 2

创建一个包含所需数据的视图，然后您可以轻松地创建一个ActiveRecord模型来访问它。

您没有提供数据的具体细节，但作为一个简单的例子，在Postgres中创建您的视图以收集数据；

  create or replace view data_trails as
    select t.*, td.trail_date from trails t
    join trail_dates td on (td.trail_id = t.id)

然后创建您的模型。

  class DataTrail < ActiveRecord::Base
    scope :on_date, -> (date) { where(trail_date: date) }
  end

  DataTrail.on_date(Date.today)

你可以在《企业级 Rails》一书中找到更多相关信息。虽然该书已经有点过时，但其中的原则仍然是可靠的。

http://enterpriserails.chak.org/full-text/chapter-11-view-backed-models

- Andy Henson

---

视图对我不起作用，因为该函数需要参数用于函数内的子查询。如果我放弃子查询，只是在函数执行后进行筛选（如此处建议），那么函数将会太慢，因为它必须计算表的所有行。 - Lem Ko