ruby 50 lines · 3 tabs

Database view-backed models for complex queries

Alex Kumar Jan 2026

3 tabs

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

3 files · ruby Explain with highlit

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.

Related snips

sql

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions

by Maria Garcia 2 tabs

ruby

class CommentsController < ApplicationController
  before_action :set_post

  def create
    @comment = @post.comments.build(comment_params)

System test: asserting Turbo Stream responses

rails hotwire turbo

by codesnips 4 tabs

ruby

class Post < ApplicationRecord
  belongs_to :author, class_name: 'User'
  has_many :comments, dependent: :destroy

  scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
  scope :draft, -> { where(published_at: nil) }

ActiveRecord scopes for reusable query logic

rails activerecord patterns

by Alex Kumar 1 tab

ruby

module Api
  module V1
    class UsersController < BaseController
      def show
        user = User.includes(:profile).find(params[:id])

ETags for conditional requests and caching

rails caching http-caching

by Alex Kumar 1 tab

ruby

class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author)
                 .order(created_at: :desc)
                 .page(params[:page])
                 .per(10)

Turbo Frames: infinite scroll with lazy-loading frame

rails turbo hotwire

by codesnips 4 tabs

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

Database view-backed models for complex queries — share card

Link copied