How to have a PostgreSQL function backing a Rails model?

Question

I have an app written in Rails and a PostgreSQL database. My app has some complex queries that I use to wrap in PostgreSQL views so that I can "plug" Rails models to them - making things simpler in the app level.

The above approach has been successful so far, given that I can use many of the ActiveRecord capabilities through those models attached to these views.

However, I'm facing a situation in which I have to optimize some of these views, but the only way I found was to transform the PostgreSQL view into a PostgreSQL function so that I can pass params to be used as constraints (in where clauses). This approach has speed up my views by a factor of 100!

The problem though is, to call these functions, I use to do something like Model.find_by_sql('SELECT * FROM my_pg_function(?, ?)') - which returns an array of Model's instance. This is not ideal, because I need method chaining (like Model.find_by_sql('SELECT * FROM my_pg_function(?, ?)').find(...)) - otherwise I'll have to make a lot of changes in my app.

Any ideas on how can I use a raw query to load my data in a chainable way?

Answer 1

You should be able to accomplish this goal using a little bit of Arel.

function = Arel::Nodes::NamedFunction.new('my_pg_function',[1,2])
subquery = Arel::SelectManager.new.project(Arel.star).from(function).as(Model.table_name)

Model.from(subquery)

This will produce the following query:

SELECT 
  models.*
FROM 
  (SELECT * FROM my_pg_function(1, 2)) models

This will allow you to dynamically substitute the arguments for the function [1,2] in this case. As well as chain additional conditions to the "table" because it will return an ActiveRecord::Relation object in the process.

So you could implement as something like:

class MyModel < ApplicationRecord
  self.table_name = :my_models 

  def self.filter(*args)
    from( 
      Arel::SelectManager.new
        .project(Arel.star)
        .from(Arel::Nodes::NamedFunction.new(
               'my_pg_function',
                args.map {|e| Arel::Nodes.build_quoted(e)}))
        .as(table_name)
    ) 
  end
end 

And use like

MyModel.filter(1,2).where(x: 12..).order(:x)