StrictPagination

A Ruby gem that adds strict pagination validation to ActiveRecord, preventing unsafe JOINs that can cause inconsistent pagination results.

Problem

When using LIMIT/OFFSET with DISTINCT and JOINs to associations that multiply rows (like has_many or unsafe has_one), pagination can become inconsistent. This happens because:

JOINs multiply rows (e.g., a User with 3 posts becomes 3 rows)
DISTINCT collapses them back to 1 row
But LIMIT is applied BEFORE DISTINCT, not after
Result: inconsistent page sizes and missing records

Solution

This gem provides strict_pagination mode that validates queries before execution, ensuring they don't use JOINs that multiply rows when using pagination.

Installation

Add this line to your application's Gemfile:

gem 'strict_pagination'

And then execute:

bundle install

Or install it yourself as:

gem install strict_pagination

Usage

Basic Usage

Simply add .strict_pagination to your ActiveRecord queries with DISTINCT:

# Safe: no associations
User.strict_pagination.distinct.page(1).per(10)

# Safe: belongs_to associations don't multiply rows
Article.strict_pagination
       .includes(:author)
       .distinct
       .page(1).per(10)

# Safe: view-backed has_one associations
Order.strict_pagination
     .includes(:latest_payment)  # backed by a database view
     .distinct
     .page(1).per(10)

# ERROR: has_many multiplies rows
User.strict_pagination
    .includes(:posts)
    .distinct
    .page(1).per(10)
# => StrictPagination::ViolationError

Note: Validation only runs when both LIMIT (from .page()) and DISTINCT are present. This is because the pagination issue only occurs with DISTINCT queries.

When is Validation Triggered?

The validation only runs when BOTH conditions are met:

Query has LIMIT (pagination)
Query has DISTINCT

This means you can use strict_pagination safely on all queries, and it will only validate when necessary.

Safe Associations

The following associations are considered safe:

belongs_to - Never multiplies rows
has_one backed by database views - Views starting with Views:: or table names starting with views_
has_one with unique constraint - When the foreign key has a unique index

Unsafe Associations

The following associations will trigger an error:

has_many - Always multiplies rows
has_and_belongs_to_many - Always multiplies rows
has_one without unique constraint - Can multiply rows

Error Messages

When a violation is detected, you'll get a detailed error message:

StrictPagination::ViolationError: Strict pagination violation: The query includes unsafe
associations `posts` that can multiply rows, causing inconsistent pagination with
LIMIT/OFFSET + DISTINCT.

Unsafe associations detected:
  - posts: has_many (always multiplies rows)

Solutions:
1. Remove `posts` from the query's includes/preload/eager_load/joins
2. Replace has_many with has_one backed by a database view (automatically safe)
3. Add a unique constraint to the foreign key (for has_one associations)
4. Remove .strict_pagination if you understand the pagination risks

Configuration

You can configure the gem's behavior using an initializer:

# config/initializers/strict_pagination.rb

StrictPagination.configure do |config|
  # Validate all paginated queries, even without DISTINCT
  # Default: false (only validates with DISTINCT)
  config.validate_on_all_queries = false

  # Add custom view prefixes to consider as safe
  # Default: [] (uses "Views::" and "views_" by default)
  config.safe_view_prefixes = ['Reports::', 'Analytics::']
end

Configuration Options

`validate_on_all_queries`

By default, validation only runs when a query has both LIMIT and DISTINCT. Set this to true to validate all paginated queries regardless of DISTINCT:

StrictPagination.configure do |config|
  config.validate_on_all_queries = true
end

# Now this will be validated even without DISTINCT
User.strict_pagination.includes(:posts).limit(10)
# => StrictPagination::ViolationError

`safe_view_prefixes`

Add custom prefixes for view classes or table names that should be considered safe:

StrictPagination.configure do |config|
  config.safe_view_prefixes = ['Reports::', 'Analytics::']
end

# Now these are considered safe
class Reports::UserSummary < ApplicationRecord; end
Order.strict_pagination.includes(:user_summary).page(1)  # Safe

Best Practices

For API Endpoints

Use strict_pagination on all paginated API endpoints with DISTINCT:

# app/controllers/api/v1/users_controller.rb
def index
  @users = User.strict_pagination
               .includes(:profile)  # Safe: belongs_to
               .distinct
               .page(params[:page])
               .per(params[:per_page])

  render json: @users
end

Using Database Views for Safe has_one

Replace has_many with has_one backed by a database view:

# Instead of:
class Author < ApplicationRecord
  has_many :books
end

# Create a view for the latest book:
# CREATE VIEW views_latest_books AS
#   SELECT DISTINCT ON (author_id) *
#   FROM books
#   ORDER BY author_id, published_at DESC

class Author < ApplicationRecord
  has_one :latest_book, class_name: 'Views::LatestBook'
end

# Now safe to use with strict_pagination:
Author.strict_pagination
      .includes(:latest_book)
      .distinct
      .page(1).per(10)

Gradual Adoption

You can gradually adopt strict pagination in your codebase:

# Start with critical endpoints
class Api::V1::OrdersController < ApplicationController
  def index
    @orders = Order.strict_pagination.distinct.page(params[:page])
  end
end

# Once confident, use it everywhere
class ApplicationRecord < ActiveRecord::Base
  # Override default scope to use strict_pagination
  def self.paginated(**options)
    strict_pagination.distinct.page(options[:page]).per(options[:per_page])
  end
end

How It Works

The gem:

Adds a strict_pagination method to ActiveRecord::Relation
Uses prepend to hook into query execution (exec_queries)
Before executing, validates that no unsafe associations are included/joined
Only validates when the query has LIMIT (and optionally DISTINCT)
Raises StrictPagination::ViolationError if unsafe associations are detected

Architecture

The gem follows Rails best practices:

ActiveSupport::Concern - For clean module inclusion
ActiveSupport.on_load - For proper Rails initialization
Railtie - For Rails lifecycle integration
Prepend pattern - For clean method overriding

Examples

Safe Queries

# No associations
User.strict_pagination.distinct.page(1).per(20)

# belongs_to only
Comment.strict_pagination
       .includes(:author, :post)
       .distinct
       .page(1).per(20)

# View-backed has_one
Invoice.strict_pagination
       .includes(:latest_payment)
       .distinct
       .page(1).per(20)

# has_one with unique constraint
User.strict_pagination
    .includes(:profile)  # profiles.user_id has unique index
    .distinct
    .page(1).per(20)

Unsafe Queries

# has_many association
User.strict_pagination
    .includes(:posts)  # Error: posts multiplies rows
    .distinct
    .page(1).per(20)

# has_and_belongs_to_many
Article.strict_pagination
       .includes(:tags)  # Error: tags multiplies rows
       .distinct
       .page(1).per(20)

# has_one without unique constraint
Account.strict_pagination
       .includes(:primary_contact)  # Error: no unique constraint
       .distinct
       .page(1).per(20)

Development

After checking out the repo, run:

bundle install

To run tests:

bundle exec rspec

To build the gem:

gem build strict_pagination.gemspec

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/hdamico/strict_pagination.

License

The gem is available as open source under the terms of the MIT License.