Glad You're Ready. Let's Get Started!

Let us know how we can contact you.

Thank you!

We'll respond shortly.

Rails and SQL Views – Part 1

The project we’re currently working on has a fairly system wide requirement to have objects in different “states” as a result of user actions.

The client wanted to be able to see both the current status, and a history of all the statuses each object has been in.

These two requirements felt very closely tied, to the point where really the status is just the most recent action.

This could be fairly easily modelled in a Rails application as such.

class Rocket < ActiveRecord::Base
  has_many :activities, class_name: "RocketActivity"

  def current_activity
    activities.order("created_at DESC").first
end # [landed, landing, cruising, launch, landed]
rocket.current_activity.status # landed

This seems like a fairly typical solution, and would provide you with a fairly easy to use accessor.


But what if you wanted to view all the current activities of all the rockets you have in your fleet.

@rockets.each do |rocket|

Works great, but creates an n+1 problem, where every line is executing a query to find all the activities and return the latest.

A solution to this is to use includes, you could define rockets as

@rockets = Rocket.scope.includes(:activities)

Still, your custom logic inside the #current_activity method is then going to have to perform more sql on that collection.

Also it shouldn’t be the Rocket’s responsibility to know how to find it’s most recent activity, that’s more of a class concern.

Given that, in Rails land as long as you had a class to take care of it, you could define that relationship as.

class Rocket < ActiveRecord::Base
  has_many :activities, class_name: "RocketActivity"
  has_one :current_activity, class_name: "RocketCurrentActivity"

Leaving us to include the current_activity and output more efficiently.

@rockets = Rocket.scope.includes(:current_activity)

And keep our view the same.

@rockets.each do |rocket|

We solved this with SQL views (Wikipedia), and next week I’m going to show you how.

Post a Comment

Your Information (Name required. Email address will not be displayed with comment.)

* Copy This Password *

* Type Or Paste Password Here *