Making complex queries readable with the ActiveRecord







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

Full class : 

class CourierQuery

 attr_accessor :result, :total_count

 def initialize(params, current_user, current_ability)
   @params = params
   @current_user = current_user
   @current_ability = current_ability
   @total_count = 0
   @result = []
 end

  def query()
    data = fields
    .includes([:customer_contracts])
    .includes([:aerocontract])
    .references(:customer_contracts)
    .references(:aerocontract)
    .where(status_filter)
    .where(courier_name_filter)
    .where(start_date_filter)
    .where(end_date_filter)
    .where(customer_contract_filter)
    .order("couriers.created_at DESC")
    .accessible_by(@current_ability)
    @total_count = JSON.parse(data.to_json).size 
    data = data.page(@params[:page])
    @result = data
    self
  end


  private

  def status_filter
    {is_active: get_status(@params[:status]) } unless   get_status(@params[:status]).nil?  end

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

  def start_date_filter
    s_date = @params[:start_date]
    return {} unless s_date.present?
    ["aero_contracts.created_at >= ?", s_date.to_i ]
  end

  def end_date_filter
    e_date = @params[:end_date]
    return {} unless e_date.present?
    ["aero_contracts.created_at < ?",e_date.to_i ] || ""
  end

 
  def customer_contract_filter

    airp_codes =  @params[:airport_codes]
    airl_codes =  @params[:airline_codes]

    if airp_codes.present?
      airp_codes = airp_codes.split(",").map{|iata| iata.strip }
      airp_ids = Airport.check_iata(iata: airp_codes).pluck(:id)
    end

    if airl_codes.present?
      airl_codes = airl_codes.split(",").map{|iata| iata.strip }
      airl_ids = Airline.check_iata(iata: airl_codes).pluck(:id)
    end

    flt = {}
    flt = flt.merge( {airport_id: airp_ids} ) if airp_ids.present?
    flt = flt.merge( {airline_id: airl_ids} ) if airl_ids.present?

    status = @params[:status]
    flt = flt.merge( {is_active: get_status(status)} ) unless get_status(status).nil?
    flt = ( {customer_contracts: flt} ) if flt.present?
    flt

  end

  def fields
    Courier.select(:id, :gid, :company_name, :address, :email, :is_active)
  end

  def get_status status
    return true if status.to_s == "true"
    return false if status.to_s == "false"
    return nil
  end


end


Comments

  1. Love this article! Useful and informative. looking forward to seeing more like this. Also check this out Courier Service Abu Dhabi To Dubai. Thank you.

    ReplyDelete

Post a Comment

Popular posts from this blog

Design a notification system

URL Shortener System Design

NETFLIX HIGH-LEVEL SYSTEM DESIGN