Making complex queries readable with the ActiveRecord

Sometimes, we need multiple tables to join in our project and these queries almost not be readable. The first thing that comes to our mind is of course Gems in Ruby on Rails. Today I will focus on how we can make complex queries more readable without using any library or Gem.

I have some models in our current project on Ruby On Rails 5.2 :

models/courier.rb

class Courier < ApplicationRecord
  has_one :main_contract, :as => :customer
  has_many :customer_contracts
  #default pagination 
  paginates_per 20 
end

models/main_contract.rb

class MainContract < ApplicationRecord
  belongs_to :customer, polymorphic: true, optional: true
  belongs_to :product
end

models/customer_contract.rb

class CustomerContract < ApplicationRecord
  belongs_to :product
  belongs_to :airline, optional: true
  belongs_to :airport, optional: true
  belongs_to :courier, optional: true
end

models/airport.rb

class Airport < ApplicationRecord
  has_many :customer_contracts
end

models/airline.rb

class Airline < ApplicationRecord
  has_many :customer_contracts
end

models/application_record.rb

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

Preparing Rest URL

For example, we have a Courier Filter and List page and this page needs Couriers data. Sometimes all parameters will come full, some will be empty, some will come full.

We can do it by the restful API.

curl --location 
--request 
GET 'api/v1/couriers?status=true&courier_name=APM&start_date=1572803794&end_date=1585763945&airport_codes=SFO,LAX&airline_codes=TK,CM&page=2' 
\ --header 'Authorization: eyJ0eXAiOiJK.eyJ1c2VyX2lkIjozNX0.VPCMWk8' 
\ --header 'Content-Type: application/json'

It is necessary to join and query couriers according to contracts, airport and airline models. We have some params in API GET URL :

status:true
courier_name: APM
start_date: 1572803794
end_date: 1585763945
airport_codes :SFO,LAX
airline_codes :TK,CM
page:1

Query

Active Record is a logical Object Relational Mapping. If you don’t put any cases inside the where({}) block, Active Record doesn't care this conditional block. For example :

Courier.select(:id, :company_name).where({}).limit(3)
Query will be as a sql : 
SELECT  "couriers"."id", "couriers"."company_name" FROM "couriers" LIMIT 3

That’s why, we can use where({}) block, if we don’t have any conditional params. I can use a lot of cases in the query method. We have 5 filter query preparing method and we can use this way :

 .where(status_filter)
 .where(courier_name_filter)
 .where(start_date_filter)
 .where(end_date_filter)
 .where(customer_contract_filter)

Self-model query

For example in courier name searching filter. If we don’t have courier name in request params we can return just empty hash block {}. If we have courier name we can return direct an ILIKE searching query string.

def courier_name_filter
  c_name = @params[:courier_name]
  return {} unless c_name.present?
  ["couriers.company_name ILIKE ?", "%#{c_name}%"]
end

Joins-model query

If there is more than one condition, we will use the merge method, like the customer contract filter method. Also, we can see, how to join the main query and we will use the hash merge method in ruby.

merge

If we send params :

{
 “status”:”true”,
 ”courier_name”:”delivery”,
 ”start_date”:”1509604278",
 ”airport_codes”:”SFO,LAX” 
}

the filter should be for contract and other tables :

{:customer_contracts=>{:airport_id=>[3386, 3371], :is_active=>true}}
["aerocontracts.created_at >= ?", 1509604278]
["couriers.company_name ILIKE ?", "%delivery%"]

and this row will be work with the right join conditions :

.where(courier_name_filter)
.where(start_date_filter)
.where(customercontract_filter)

multiple join method

And finally our main CourierQuery.rb class :