How to use Query Objects to refactor Rails SQL-queries

Illustration of a person holding a device with "SEARCH" on it, looking intrigued at a cat wearing a helmet with an antenna, suggesting a humorous or whimsical scenario.

It’s been more than a year since my last article about service objects. It’s time to move on!

In one of the issues of Ruby weekly I came across a cool article about the command pattern. In the very beginning it’s noted that:

The command pattern is sometimes called a service object, an operation, an action, and probably more names that I’m not aware of. Whatever the name we gave it, the purpose of such a pattern is rather simple: take a business action and put it behind an object with a simple interface.

Off the record, the article about interactors is about the same thing. They are called operations in Trailblazer, and the closest variant in a dry-rb ecosystem is a dry-transaction.

I once mentioned ‘service objects’ in Telegram chat and some fella picked on this word and started interrogating me what they are. In the end we agreed on the term PORO (Plain Old Ruby Object) for them and I’m cool with that. It’s basically just a simple object, no magic at all. I call them ‘service’ just because they ‘serve’, perform some simple task and hide logic inside of them when they’re done.

There are some other services besides interactors. There’s a gazillion of them, more than you can imagine. For example:

interactors (we know about them from my article A couple of words about interactors in Rails, we recommend you to read it!);
decorators (they were explained in Not the full truth about decorators, you can read this one too, if you haven’t yet);
presenters;
serializers;
policies;
queries

And so on.

Besides that in my projects I also distinguish services like reports, cells in Trailblazer, metrics.

And of course, there are plenty of familiar services, which we use regularly but do not consider them to be Rails service objects:

uploaders (carrierwave);
validators (active-model);
jobs/workers (activejob/sidekiq);
subscribers (wisper);
enums (classy-enum).

And so on. It’s clear that the list might go on, but for today we have a topic to discuss and it’s about Query-objects.

Query-object

So, what is a query-object? It’s an object which allows writing big and complex SQL-query with ORM. And when we talk about Rails, we mean ActiveRecord.

query-object

Let’s look at how to use this class using an Internet store and its merchandise list as an example. First of all, let’s see how it might look in the controller and then with the service object.

class CatalogController < ApplicationController
  def index
    @products = Product.all
  end
end

This is how the selection of the whole products list is going to look. But we don’t need everything for sure, but only the products on this page (so let’s remember about pagination).

def index
  page_number = params[:page] || 0
  @products = Product.page(page_number)
end

We should also remember that the customer should be able to sort the products by the price.

def index
  price_sort_direction = params[:price_sort_direction].to_sym || :desc
  page_number = params[:page] || 0
  @products = Product.order(price: price_sort_direction).page(page_number)
end

But they can be sorted not by the price only.

def index
  sort_direction = params[:sort_direction].to_sym || :desc
  sort_type = params[:sort_type].to_sym || :price
  page_number = params[:page] || 0
  @products = Product.order(sort_type => sort_direction).page(page_number)
end

It should be definitely possible to choose the products from some specific category.

def index
  @products = Product.all

  category_id = params[:category_id]
  @products = @products.where(category_id: category_id) if category_id

  # ... Here I’ve hidden all the previous code, which is still here for sure...
end

Let’s add some other parameters for product filtration.

def index
  @products = Product.all

  property_ids = params[:properties]
  if properties
    @products = @products.joins(:product_properties)
                         .where(property_id: property_ids)
  end

  # ... Here I’ve hidden all the previous code, which is still here for sure...
end

Should we also add a price range?

def index
  @products = Product.all

  from_price = params[:from_price]
  @products = @products.where('price > ?', from_price) if from_price

  to_price = params[:to_price]
  @products = @products.where('price < ?', to_price) if to_price

  # ... Here I’ve hidden all the previous code, which is still here for sure...
end

And we can never make do without a search line. So let’s make it possible to type some letters and see a matching word in a product name.

def index
  @products = Product.all

  search = params[:search]
  @products = @products.where("title ILIKE '%?%'", search) if search

  # ... Here I’ve hidden all the previous code, which is still here for sure...
end

Here it is. Just a couple of iterations and our simplest sorted catalogue is done. Let’s have a look at the result:

def index
  @products = Product.all

  search = params[:search]
  @products = @products.where("title ILIKE '%?%'", search) if search

  from_price = params[:from_price]
  @products = @products.where('price > ?', from_price) if from_price

  to_price = params[:to_price]
  @products = @products.where('price < ?', to_price) if to_price

  property_ids = params[:properties]
  if properties
    @products = @products.joins(:product_properties)
                         .where(property_id: property_ids)
  end

  category_id = params[:category_id]
  @products = @products.where(category_id: category_id) if category_id

  sort_direction = params[:sort_direction].to_sym || :desc
  sort_type = params[:sort_type].to_sym || :price
  page_number = params[:page] || 0
  @products = @products.order(sort_type => sort_direction).page(page_number)
end

It’s neat enough, considering that not all the variants but only the basic ones are mentioned here. Moreover, I have some experience in writing things like that and the odder and more cumbersome variants are quite common.

And if you look at all the action here, you’ll see that it’s simply a large database query.

Query-object

Well, let’s let’s put all that into a separate class, which we will call. Thus we will hide all the logic behind a‘concise’ name FindProducts. Here’s how our controller will look:

class CatalogController < ApplicationController
  def index
    @products = FindProducts.new(Product.all).call(permitted_params)
  end

  def permitted_params
    params.permit(:search, :from_price, :to_price,
                  :properties, :category_id,
                  :sort_direction, :sort_type, :page)
  end
end

And here’s how our object may look:

# app/queries/find_products.rb
class FindProducts
  attr_accessor :initial_scope

  def initialize(initial_scope)
    @initial_scope = initial_scope
  end

  def call(params)
    scoped = search(initial_scope, params[:search])
    scoped = filter_by_price(scoped, params[:from_price], params[:to_price])
    scoped = filter_by_properties(scoped, params[:properties])
    scoped = filter_by_category(scoped, params[:category_id])
    scoped = sort(scoped, params[:sort_type], params[:sort_direction]
    scoped = paginate(scoped, params[:page]
    scoped
  end

  private def search(scoped, query = nil)
    query ? scoped.where("title ILIKE '%?%'", query) : scoped
  end

  private def filter_by_price(scoped, from = nil, to = nil)
    from_price ? scoped.where('price > ?', from_price) : scoped
    to_price ? scoped.where('price < ?', to_price) : scoped
  end

  private def filter_by_properties(scoped, properties = nil)
    if properties
      scoped.joins(:product_properties).where(property_id: properties)
    else
      scoped
    end
  end

  private def filter_by_category(scoped, category_id = nil)
    category_id ? scoped.where(category_id: category_id) : scoped
  end

  private def sort(scoped, sort_type = :desc, sort_direction = :price)
    scoped.order(sort_type => sort_direction)
  end

  private def paginate(scoped, page_number = 0)
    scoped.page(page_number)
  end
end

Thus the class is rather large, but one class is one task and it’s also easier to read. All the logic is explained in a call method:

first of all we do a search in a default selection;
then we filter by the price;
filter by the parameters;
filter by the categories;
sort;
and finally we get a needed page.

I think I should also explain the default_scope. It’s possible to write Product.all right in this class, but to my mind we managed to make our service more flexible. For instance, we can show only the products available or some product scope for a specified area (according to the geolocation). Of course, all the aforementioned might be added to this service object.

Query-object specs

The service objects are among our favourites because they are really easy to test (unlike the controller specs or many other ones). But my way of testing query-object was sometimes criticized. This is what I do: I check if there’s one line or another in a resulting query.

Example:

RSpec.describe FindProducts do
  let(:initial_scope) { Product.all }

  let(:params) { {} }

  subject { described_class.new(initial_scope).call(params) }

  context 'with empty params' do
    it 'sorts' do
      expect(subject.to_sql).to include('ORDER BY "products"."price" DESC')
    end

    it 'paginates' do
      expect(subject.to_sql).to include('LIMIT')
      expect(subject.to_sql).to include('OFFSET')
    end
  end
end

Which means that I create SQL from ActiveRecord::Relation object and check if with the needed parameters there’s one line or another in the resulting SQL. It’s criticized because I actually don’t test the result, as I might have made a mistake in SQL, and then the specs will not check anything.

Opposite to that might be creating a real object in the database, when the real request is executed and the objects are created. After that, you just need to compare the resulting objects with the expected ones.