Scopes, Sailing the ActiveRecord

If you spend any amount of time developing web applications with Ruby then you will likely know of the gem ActiveRecord. It powers the SQL entity relation management, query generation, and query execution for Ruby on Rails and can also be included in any other Ruby projects that require database manipulation.

I like to think of ActiveRecord as my own little pirate ship, she helps me navigate the murky and sometimes inconsistent waters of the DBMS, Database Management System, world and lets me plunder databases of all shapes and sizes through one common method, the boarding plank. No matter the type of database I am plundering I can always rely on the fact that ActiveRecord will let me do in a consistent and familiar way, swords ready.

Like a ship she is only truly effective if you know how to sail her.

image

Lately, I find myself spending a lot of time refactoring and optimizing ActiveRecord database queries and I have learned a few tricks, one of which is the power of scopes, not telescopes you scallywag.

These have helped me tame some of the most fearsome queries that I have had in my life as a database swashbuckler. The basic idea is that you break common queries of a model into methods on that class and then chain them together to make complex, yet easily understood queries.

The best way to explain this is with an example. Say you have a model Ship and people can be a Sailor.

class Ship < ActiveRecord::Base  
  has_many :sailors
end  
class Sailor < ActiveRecord::Base  
  belongs_to :ship
end  

So that's a pretty reasonable situation for a pirate to have. As captain of a ship you want to see all of your crew, so we will need an index view!

module Api  
  class SailorsJobApplicationsController < BaseController

    # GET /api/sailors
    def index
      @sailors = Sailor.where(query_params)

      respond_with @sailors
    end

  private

    def query_params
      params.permit(:ship_id)
    end

  end
end  

Nothing fancy. We query for all Sailor records with a ship_id of whatever is passed into the query string. That's all well and good, but you're a successful captain and you keep capturing ships left and right for your fleet and any sailor can be part of multiple crews, how can we still list who is a Sailor for each Ship then?

class Ship < ActiveRecord::Base  
  has_many :ranks
  has_many :sailors, through: :ranks
end  
class Rank < ActiveRecord::Base  
  belongs_to :ship
  belongs_to :sailor
end  
class Sailor < ActiveRecord::Base  
  has_many :ranks
  has_many :ships, through: :ranks
end  

Well awesome, but now we need to update our index method.

# GET /api/sailors
def index  
  @sailors = Sailor

  if params[:ship_id]
    @sailors.joins(:ships).where(ships: { id: params[:ship_id] })
  end

  respond_with @sailors
end  

So nothing terribly special, we just join the relationship and filter on it. What happens though when we need to start also filtering fields on Sailor, say has_scurvy:boolean and on_duty:boolean?

# GET /api/sailors
def index  
  @sailors = Sailor

  if params[:ship_id]
    @sailors.joins(:ships).where(ships: { id: params[:ship_id] })
  end

  if params[:has_scurvy]
    @sailors.where(has_scurvy: params[:has_scurvy])
  end

  if params[:on_duty]
    @sailors.where(on_duty: params[:on_duty])
  end

  respond_with @sailors
end  

So that works out well, but now we also need to be able to filter based on whether or not a sailor has a parrot as a pet that is alive, which is it's own model. Also whether or not a ship that a sailor is a crew of has sank or not.

class Pet < ActiveRecord::Base  
  belongs_to :sailor
end  
class Sailor < ActiveRecord::Base  
  ...
  has_many :pets
end  
# GET /api/sailors
def index  
  @sailors = Sailor

  if params[:has_parrot]
    @sailors = @sailors.joins(:pets).where(pets: { type: "parrot", alive: true })
  end

  if params[:ship_id]
    @sailors = @sailors.joins(:ships).where(ships: { id: params[:ship_id], has_sunken: params[:ship_has_sunken] })
  end

  if params[:has_scurvy]
    @sailors = @sailors.where(has_scurvy: params[:has_scurvy])
  end

  if params[:on_duty]
    @sailors = @sailors.where(on_duty: params[:on_duty])
  end

  respond_with @sailors
end  

Woah, this is getting a bit cumbersome. There has to be a better way and with ActiveRecord of course there is! We'll need to make some changes though.

class Sailor < ActiveRecord::Base  
  has_many :ranks
  has_many :ships, through: :ranks

  scope :healthy, -> { where(has_scurvy: false) }
  scope :parrot_owner, -> { joins(:pets).where(pets: { type: "parrot", alive: true }) }
  scope :scurvied, -> { where(has_scurvy: true) }

  def self.crew_of(ship_id: nil, sunken: false)
    crew = self.joins(:ships).where(ships: { has_sunken: sunken })

    unless ship_id.nil?
      crew = crew.where(ships: { id: ship_id })
    end

    crew
  end
end  
# GET /api/sailors
def index  
  @sailors = Sailor

  @sailors = @sailors.parrot_owner if params[:has_parrot]

  @sailors = @sailors.crew_of(ship_id: params[:ship_id], sunken: params[:ship_has_sunken])

  if params[:has_scurvy]
    @sailors = @sailors.scurvied
  else
    @sailors = @sailor.healthy
  end

  if params[:on_duty]
    @sailors = @sailors.where(on_duty: params[:on_duty])
  end

  respond_with @sailors
end  

Well look'ye there. That is much more readable and understandable. Now obviously this is a contrived example ... pirates didn't have computers, let alone fancy Rails applications like this one! However, I'm sure you will see how this can be applied to your own code.

Happy Sailing

There you go! You now know one of my tricks, I hope it helps and that you use your new found pirate skills for good. For more detailed information check the Rails Guides.