1_model.rb

class SalesDashboardYTDByCustomer < ActiveRecord::Base
  include Tablelessable

  column :user_id
  column :customer_id
  column :customer_name
  column :actual_sales
  column :planned_sales
  column :sales_difference
  column :actual_profit
  column :planned_profit
  column :profit_difference
  column :actual_margin
  column :planned_margin

  def self.view # rubocop:disable Metrics/MethodLength
    <<-SQL
      SELECT
         u.id AS user_id
        ,c.id AS customer_id
        ,c.name AS customer_name
        ,COALESCE((abc).measure.sales,0) AS actual_sales
        ,(pac).measure.sales AS planned_sales
        ,COALESCE((abc).measure.sales,0) - COALESCE((pac).measure.sales,0) AS sales_difference
        ,COALESCE((abc).measure.profit,0) AS actual_profit
        ,(pac).measure.profit AS planned_profit
        ,COALESCE((abc).measure.profit,0) - COALESCE((pac).measure.profit,0) AS profit_difference
        ,COALESCE((abc).measure.margin,0) AS actual_margin
        ,(pac).measure.margin AS planned_margin
      FROM public.users u
      CROSS JOIN public.customers_for_user_period(u.id, 'YTD') cfu
      JOIN public.customers c ON c.id = cfu
      LEFT JOIN public.actual_amounts_for_user_and_period_by_customer(u.id, 'YTD') abc ON abc.customer_id = c.id
      LEFT JOIN public.ytd_planned_amounts_by_customer(u.id) pac ON pac.customer_id = c.id
    SQL
  end
end

tableless.rb

require 'securerandom'

module Tableless
  def self.included(klass)
    klass.instance_eval do
      extend ClassMethods

      self.table_name = "#{name.tableize}_#{SecureRandom.hex(6)}"

      default_scope { arelized_query }
    end
  end

  module ClassMethods
    def arelized_query
      from(Arel.sql("(#{query_sql}) #{table_name}"))
    end

    def query_sql
      @query ||= File.read(query_path)
    end

    def query_path
      "./db/sql/queries/#{name.tableize}.sql"
    end

    def create_temporary_view_sql
      <<~"SQL".chomp
        CREATE TEMPORARY VIEW #{table_name} AS
        #{query_sql}
        ;
      SQL
    end

    def drop_temporary_view_sql
      <<~"SQL".chomp
        DROP VIEW #{table_name};
      SQL
    end

    def load_schema!
      return super if @columns_hash

      connection.exec_query create_temporary_view_sql
      super
      connection.exec_query drop_temporary_view_sql
    end
  end
end

tablelessable1.rb

module Tablelessable
  def self.included(klass)
    klass.instance_eval do
      extend ClassMethods
      default_scope { from(Arel.sql("(#{view}) as #{table_name}")) }
      self.table_name = 'tablelessable' #this shouldn't exist ;)

      def view
        @view ||= File.read(view_path)
      end

      def view_path
        file = File.join(
          Rails.root,
          'db/sql/views',
          "#{name.underscore}.sql"
        )
      end
    end
  end

  module ClassMethods
    def column(name, sql_type = nil, default = nil, null = true)
      @columns ||= []
      columns << ActiveRecord::ConnectionAdapters::Column.new(
        name.to_s,
        default,
        sql_type.to_s,
        null
      )
    end
  end
end