使用Postgres函数作为ActiveRecord模型

I have created a Postgres function that I am using in order to perform a complex query which joins many tables that all have to be filtered by a dynamic date field.

我创建了一个Postgres函数,我正在使用它来执行一个复杂的查询,该查询连接了许多必须由动态日期字段过滤的表。

The function works perfectly, and allows my to perform a query like "SELECT * FROM trail_for_date('2014-01-01')" and returns a table.

该函数完美运行,并允许我执行查询,如“SELECT * FROM trail_for_date('2014-01-01')”并返回一个表。

A simplified example from the Postgres documentation on functions:

关于函数的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;

How could I use this return table as a Rails/Ruby model where the argument to the function is dynamic?

我怎么能将这个返回表用作Rails / Ruby模型,其中函数的参数是动态的?

Something like the following (which obviously doesn't work):

像下面的东西(显然不起作用):

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

2 个解决方案

#1

Create a view containing the data you need and then you can easily create an ActiveRecord model to access it.

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

You haven't provided specific details of your data but as a simple example, create your view in Postgres to collect your data;

您尚未提供数据的具体详细信息,但作为一个简单示例,请在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)

Then create your model

然后创建您的模型

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

  DataTrail.on_date(Date.today)

You can find more information in the Enterprise Rails book. It's getting a little dated now but the principles are sound.

您可以在Enterprise Rails一书中找到更多信息。它现在变得有点过时,但原则是合理的。

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

#2

create a dummy table with the same columns as your function output:

创建一个与函数输出具有相同列的虚拟表:

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

Create your model:

创建你的模型:

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

Reference it in your controller and populate it with the function call results:

在控制器中引用它并使用函数调用结果填充它:

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

#1