Query Object in Ruby on Rails

05 October, 2016

Database queries are common when you develop web applications. Ruby on Rails and it’s ActiveRecord liberates you from writing tons of boilerplate SQL code and results in creation of elegant, eloquent queries in plain Ruby.

But plenty of immense possibilities that Ruby and ActiveRecord provide, unfortunately, remain unused. I bet that often you see a lot of enormous scopes in Ruby on Rails models, endless chains of queries in controllers and even bulky chunks of raw SQL.

  @articles = Article
              .includes(:user)
              .order("created_at DESC")
              .where("text IS NOT NULL")
              .page(page)

  @articles = Articles
              .connection
              .select_all(%Q{SELECT  articles.* FROM articles
                          WHERE (text IS NOT NULL)  ORDER BY
                          created_at DESC LIMIT 5 OFFSET 0})

Bad cases of using ActiveRecord queries

These poor practices may create obstacles and become a reason of developer’s headaches in the real-world web applications.

Typical DB queries application problems:

Solution:

These problems can be solved by using Query Object pattern — a common technique that isolates your complex queries.

Query Object in ideal case is a separate class that contains one specific query that implements just one business logic rule.

Implementation:

For most of the cases Query Object is PORO that accepts relation in constructor and defines queries named like an ActiveRecord common methods:

  # app/models/article.rb
  class Article < ActiveRecord::Base
    scope :by_title, ->(direction) { order title: direction }
    scope :by_date, ->(direction) { order created_at: direction }
    scope :by_author, ->(direction) { order "users.full_name #{direction}" }
  end

  # app/queries/ordered_articles_query.rb
  class OrderedArticlesQuery
    SORT_OPTIONS = %w(by_date by_title by_author).freeze

    def initialize(params = {}, relation = Article.includes(:user))
      @relation = relation
      @params = params
    end

    def all
      @relation.public_send(sort_by, direction)
    end

    private

    def sort_by
      @params[:sort].presence_in(SORT_OPTIONS) || :by_date
    end

    def direction
      @params[:direction] == "asc" ? :asc : :desc
    end
  end

  # app/controllers/articles_controller.rb
  class ArticlesController
    def index
      @articles = OrderedArticlesQuery.new(sort_query_params).all.page(params[:page])
    end

    private

    def sort_query_params
      params.slice(:sort_by, :direction)
    end
  end

Query Object implementation and usage in controller

HEREDOC syntax for raw SQL:

For the cases where you desperately need to use raw SQL code try to isolate it using Ruby’s HEREDOC syntax:

  class PopularArticlesQuery
    POPULAR_TRESHOLD = 5
   
    def initialize(subscriptions = Subscription.all)
      @subscriptions = subscriptions
    end
   
    def all
      @subscriptions.where(query)
    end
   
    private
   
    def query
      <<-SQL
        articles.comments_count >= #{POPULAR_TRESHOLD}
        AND articles.content IS NOT NULL
        AND articles.status = #{Article::STATUSES[:published]}
        ORDER BY articles.comments_count DESC
      SQL
    end
  end

HEREDOC syntax example for raw SQL inserts

Extending scope:

If your scope relates to existing QueryObject, you can easily extend its relation instead of cluttering up your models. ActiveRecord::QueryMethods.extending method will help you:

  class OrderedArticlesQuery
    SORT_OPTIONS = %w(by_date by_title by_author).freeze
   
    def initialize(params = {}, relation = Article.includes(:user))
      @relation = relation.extending(Scopes)
   
      @params = params
    end
   
    def all
      @relation.public_send(sort_by, direction)
    end
   
    private
   
    def sort_by
      @params[:sort].presence_in(SORT_OPTIONS) || :by_date
    end
   
    def direction
      @params[:direction] == "asc" ? :asc : :desc
    end
   
    # Group additional scope methods in module in order to extend relation
    module Scopes
      def by_title(direction)
        order(title: direction)
      end
   
      def by_date(direction)
        order(created_at: direction)
      end
   
      def by_author
        order("users.full_name #{direction}")
      end
    end
  end

Extending scope for Query Objects relations

Composing Query Objects:

Query Objects should be devised to support composition with other Query Objects and other ActiveRecord relations. In the example below two composed Query Objects represent one SQL query:

  class FeaturedQuery
    def initialize(relation = Article.all)
      @relation = relation
    end
   
    def all
      @relation.where(featured: true).where("views_count > ?", 100)
    end
  end

  class ArticlesController
    def index
      @articles = FeaturedArticlesQuery.new(sorted_articles).all
      #  SELECT  "articles".* FROM "articles" WHERE "articles"."featured" = $1
      # AND (views_count > 100) ORDER BY "articles"."created_at" DESC LIMIT 10 OFFSET 0  [["featured", "t"]]
    end
   
    private
   
    def sorted_articles
      SortedArticlesQuery.new(sort_query_params).all
    end
   
    def sort_query_params
      { sort: :by_title, direction: :desc }
    end
  end

Composing two Query Objects

Inheritance of Query Objects:

If you have similar queries you may want them to be inherited to reduce repetition and follow DRY principle:

  class ArticlesQuery
    TEXT_LENGTH = 3
   
    def initialize(comments = Comment.all)
      @comments = comments
    end
   
    def all
      comments
        .where("user_id IS NOT NULL")
        .where("LENGTH(content) #{condition}")
    end
   
    def condition
      "> #{TEXT_LENGTH}"
    end
  end
   
  class LongArticlesQuery < ArticlesQuery
    TEXT_LENGTH = 5
   
    def condition
      ">= #{TEXT_LENGTH}"
    end
  end

Inheritance of Query Objects

Testing Query Objects:

Query Objects should be designed to be pleasant for testing. In most cases you just need to test core methods defined in query for their results:

  require "rails_helper"
   
  describe LongArticlesQuery do
    describe "#all" do
      subject(:all) { described_class.new.all }
    
      before do
        create :article, text: "abc"
        create :article, text: "this is long article"
      end
       
      it "returns one short comment" do
        expect(all.size).to eq(1)
      end
    end
  end

Testing Query Objects

Summary:

Good Query Object:

Use Query Objects when:

Don’t use Query Objects when:

I hope this article will help you to build awesome queries in your applications. Good luck and happy coding!