1

I have a complicated SQL query that aggregates columns using a group_by.

IndividualOrder.select(SUM(...) as amount, other_fields).group_by("organization.id")

The problem is: I get an ActiveRecord::Relation from IndividualOrder, which isn't really what the result is conceptually anymore. I'm not entirely sure how to cast it to a new method. If I use arel to handle it, I still usually would have to go IndividualOrder.arel_table, and it would still cast to whatever I select.

I just want to take the fields [:amount, :organization, :other] and be able to interact with them as I would a database backed model that had those values as a table.

So, it's not quite a tableless model (which usually aren't database backed), and it's not an actual model because it's a generated query.

Is this the use case for scenic? I'm stuck with having to navigate around 2 variables that exist within the query that I'm doing via ActiveRecord.

FullOnFlatWhite
  • 3,642
  • 2
  • 18
  • 23
  • 1
    Make a database view, use the view to back the model. It will be read only, but will otherwise work as an Active Record model. – dbugger Dec 16 '20 at 23:44

1 Answers1

1

Hi there hope this answer helps you, this should be a comment not a answer( not enough karma for doing that yet)

what you can do is to create a view in your database,

rails g migration my_new_view

ActiveRecord does not give us any methods to create views so you will have to do "manually" or you can use specific gems for doing those.

 class MyNewView < ActiveRecord::Migration[5.2]

  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          CREATE OR REPLACE VIEW public.my_new_view AS

            # your sql query here SELECT ....
            SQL
      end

      dir.down do
        execute <<-SQL
          DROP VIEW IF EXISTS public.my_new_view;
        SQL
      end
    end
  end

end

now you can refer in your model.

Beware view tables will not show in schema by default, for seing them you have to put in your application.rb:

config.active_record.schema_format = :sql

cheers.

Diogo Queiroz
  • 11
  • 4