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