How to use Query Objects to refactor Rails SQL-queries
Tags: ruby, rails, sql
Published: 2019-10-11 10:15:34

---
title: How to use Query Objects to refactor Rails SQL-queries
published: true
description: How to use Query Objects to refactor Rails SQL-queries
tags: Ruby, Rails, SQL
cover_image: https://assets.mkdev.me/posts/covers/000/000/149/original/how-to-use-query-objects-to-refactor-rails-sql-queries.png
canonical_url: https://mkdev.me/en/posts/how-to-use-query-objects-to-refactor-rails-sql-queries
---

In one of the issues of [Ruby weekly](http://rubyweekly.com/issues/365) I came across a cool article about the [command pattern](https://drivy.engineering/code_simplicity_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](https://mkdev.me/en/posts/a-couple-of-words-about-interactors-in-rails) 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](https://mkdev.me/en/posts/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](https://mkdev.me/en/posts/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.

```ruby
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).

```ruby
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.

```ruby
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.

```ruby
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.

```ruby
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.

```ruby
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?

```ruby
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.

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
# 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, parmas[: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:

1. first of all we do a search in a default selection;
2. then we filter by the price;
3. filter by the parameters;
4. filter by the categories;
5. sort;
6. 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:

```ruby
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.

***

*This is [an mkdev article](https://mkdev.me/en/posts/how-to-use-query-objects-to-refactor-rails-sql-queries) written by [Ivan Shamatov](https://mkdev.me/en/mentors/IvanShamatov). You can hire Ivan to be your personal RoR Mentor.*