ruby rails query-objects design-patterns activerecord sql

Query objects for complex database queries

Sarah Mitchell Feb 2026

2 tabs

class UserSearchQuery
  def initialize(relation = User.all)
    @relation = relation
  end

  def call(params)
    @relation = filter_by_status(params[:status])
    @relation = filter_by_role(params[:role])
    @relation = search_by_term(params[:search])
    @relation = filter_by_date_range(params[:created_after], params[:created_before])
    @relation = sort_results(params[:sort], params[:direction])
    @relation
  end

  private

  def filter_by_status(status)
    return @relation if status.blank?

    @relation.where(status: status)
  end

  def filter_by_role(role)
    return @relation if role.blank?

    @relation.where(role: role)
  end

  def search_by_term(term)
    return @relation if term.blank?

    @relation.where(
      "name ILIKE :term OR email ILIKE :term",
      term: "%#{term}%"
    )
  end

  def filter_by_date_range(start_date, end_date)
    @relation = @relation.where('created_at >= ?', start_date) if start_date.present?
    @relation = @relation.where('created_at <= ?', end_date) if end_date.present?
    @relation
  end

  def sort_results(sort_column, direction)
    return @relation.order(created_at: :desc) if sort_column.blank?

    direction = direction&.downcase == 'asc' ? :asc : :desc
    @relation.order(sort_column => direction)
  end
end

# Usage in controller:
# @users = UserSearchQuery.new.call(search_params)
# @users = UserSearchQuery.new(User.active).call(search_params)

class PostAnalyticsQuery
  attr_reader :relation

  def initialize(relation = Post.all)
    @relation = relation
  end

  def popular_posts(timeframe: 1.week.ago)
    @relation
      .where('created_at >= ?', timeframe)
      .where('views_count > ?', 1000)
      .order(views_count: :desc)
  end

  def trending_posts
    @relation
      .joins(:comments)
      .where('comments.created_at >= ?', 24.hours.ago)
      .group('posts.id')
      .having('COUNT(comments.id) > ?', 5)
      .order('COUNT(comments.id) DESC')
  end

  def by_category_stats
    @relation
      .joins(:category)
      .group('categories.name')
      .select(
        'categories.name as category_name',
        'COUNT(posts.id) as post_count',
        'AVG(posts.views_count) as avg_views',
        'MAX(posts.views_count) as max_views'
      )
  end

  def author_performance(min_posts: 5)
    @relation
      .joins(:user)
      .group('users.id')
      .having('COUNT(posts.id) >= ?', min_posts)
      .select(
        'users.name',
        'COUNT(posts.id) as total_posts',
        'SUM(posts.views_count) as total_views',
        'AVG(posts.views_count) as avg_views_per_post'
      )
      .order('total_views DESC')
  end

  def search_with_relevance(term)
    @relation
      .where("title ILIKE :term OR content ILIKE :term", term: "%#{term}%")
      .select(
        'posts.*',
        "CASE
           WHEN title ILIKE :exact THEN 3
           WHEN title ILIKE :term THEN 2
           WHEN content ILIKE :term THEN 1
           ELSE 0
         END as relevance",
        exact: term,
        term: "%#{term}%"
      )
      .order('relevance DESC, views_count DESC')
  end
end

# Chainable query objects
class BaseQuery
  attr_reader :relation

  def initialize(relation)
    @relation = relation
  end

  def call
    @relation
  end
end

class ActivePostsQuery < BaseQuery
  def call
    @relation.where(status: 'published')
  end
end

class RecentPostsQuery < BaseQuery
  def call(days = 7)
    @relation.where('created_at >= ?', days.days.ago)
  end
end

# Chaining:
# posts = Post.all
# posts = ActivePostsQuery.new(posts).call
# posts = RecentPostsQuery.new(posts).call(30)

2 files · ruby Explain with highlit

Query objects encapsulate complex database queries in reusable, testable classes. I use query objects when scopes become too complex or require parameters. Query objects compose smaller scopes, handle conditionals, and apply filtering logic. They're initialized with a base relation and parameters, returning an ActiveRecord relation. Query objects keep models clean and queries maintainable. They enable query reuse across contexts—controllers, jobs, services. Testing query objects is straightforward without complex setup. Naming query objects after their purpose improves code clarity. Following composition over inheritance, query objects build complex queries from simple parts. They're essential for applications with sophisticated search and filtering requirements.