Self-Referential Associations, AKA Self Joins

Week 2 of learning Rails. After 6 weeks at Flatiron, I feel like a beginner again, but not quite like I’m stranded on a deserted island — more like I’m Link, and although I wield a wooden sword, new items abound and I’m eager to learn how to use them.

Although the Rails Guides have been invaluable, trying new constructs myself is how I internalize a concept. This blog post will be about associations, particularly self joins.

Mini-Project: Self-Joins

I created a basic rails app and deployed it on Heroku here. Try it out to see how the customer data behaves. Imagine what the schema (or “table”) might look like and then read on.

What is a Self Join?

Below is a self-join. While foreign keys are usually used to link data between two tables, you can use foreign keys to refer to data within the same table to create one that looks something like this:

(source site)

I originally tried the following associations below.

customer.rb BROKEN VERSION
1
2
3
4
class Customer < ActiveRecord::Base
  has_many :referrals, class_name: "Customer", foreign_key: "referring_customer_id", conditions: {:referring_customer_id => :id}
  belongs_to :referring_customer, class_name: "Customer", foreign_key: "referring_customer_id"
end

This didn’t work. After some serious research, stackoverflow, posting my own stackoverflow question (thanks Peter!!!), trial-and-error, and pry sessions, I refactored my code to:

customer.rb WORKING VERSION
1
2
3
4
class Customer < ActiveRecord::Base
  has_many :referrals, class_name: "Customer", foreign_key: "referring_customer_id"
  belongs_to :referring_customer, class_name: "Customer"
end

Let’s think about this for a second. Why does this work and what does it do? has_many and belongs_to provide Customers with a set of methods to access their associated data. The options passed often do not change the database since Rails tries to keep logic defined within the application. Why? I’m not exactly sure but I suppose this is partially why Rails easily supports a wide range of databases.

Here’s what the arguments do:

  • class_name: "Customer" tells Rails to look in the Customer class for the association. What do I mean by association? In has_many :referrals, the :referrals is called the assocation. When would I use this? When I need to override the Rails convention for identifying the class in which to find the association.
  • foreign_key: "referring_customer_id" tells Rails where to find the foreign key. When would I use this? When I need to override the Rails convention for identifying the class in which to find the association. Why didn’t I use it for belongs_to? It is implicitly defined with that method.
  • conditions: "referring_customer_id = id" actually fires a WHERE SQL statement with the given value. I originally used this thinking it would match referring customers by their id. It turned out to be an extra constraint though — AND (referring_customer_id = id) — which filtered for any customer who referred him/herself.

When else can I use self-joins?

Generally you can use self-joins anytime you need to create a hierarchy-like, “nested” structure.

Some other applications could be:

  • Creating parent and child directories for Folder objects.
  • Creating managers and subordinates for Employee objects.
  • Creating callers and receivers for PhoneTreeMember objects.
  • Anything else with a parent-child data relationship.

The Master Sword!

Just when you thought THAT was exciting, there are also plenty of GEMS that allow you to create these nested structures! Two from the Ruby Toolbox are Awesome Nested Set and Ancestry.

Comments