Database view-backed models for complex queries

11893 views

                  class CreateUserStatsView < ActiveRecord::Migration[6.1]
  def up
    execute <<-SQL
      CREATE VIEW user_stats AS
      SELECT
        users.id AS user_id,
        users.name,
        users.email,
        COUNT(DISTINCT posts.id) AS posts_count,
        COUNT(DISTINCT comments.id) AS comments_count,
        SUM(posts.views) AS total_views,
        SUM(posts.likes_count) AS total_likes,
        MAX(posts.created_at) AS last_post_at
      FROM users
      LEFT JOIN posts ON posts.author_id = users.id
      LEFT JOIN comments ON comments.author_id = users.id
      GROUP BY users.id, users.name, users.email;
    SQL
  end

  def down
    execute 'DROP VIEW user_stats;'
  end
end

                  class UserStat < ApplicationRecord
  self.table_name = 'user_stats'
  self.primary_key = 'user_id'

  # This is a read-only view
  def readonly?
    true
  end

  # Optional: Add association back to user
  belongs_to :user, foreign_key: :user_id
end

                  module Api
  module V1
    class LeaderboardController < BaseController
      def index
        leaders = UserStat
                    .where('posts_count > 0')
                    .order(total_views: :desc)
                    .limit(100)

        render json: leaders
      end
    end
  end
end

Complex reporting queries with multiple joins and aggregations can become unmaintainable in ActiveRecord. PostgreSQL views encapsulate query complexity in the database layer and appear as regular tables to Rails. I create views for common reporting needs like materialized user statistics, denormalized search indexes, or aggregated analytics. Views update automatically as underlying data changes (or on schedule for materialized views). ActiveRecord models backed by views work exactly like regular models—I can query them with where, includes, and other ActiveRecord methods. The key limitation is that views are read-only by default. For complex analytics, materialized views with scheduled refreshes balance freshness and query performance.

Alex Kumar

More from Alex Kumar

Database view-backed models for complex queries

0 Comments

More from Alex Kumar