Self-Referential Associations, AKA Self Joins
The following is a guest post by Daniel Chang and originally appeared on his blog. Daniel is currently in the Ruby-003 class at The Flatiron School. You can follow him on Twitter here. 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 […]
Reading Time 2 mins
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.
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:
I originally tried the following associations below.
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:
Let’s think about this for a second. Why does this work and what does it do?
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
Customerclass for the association. What do I mean by association? In
has_many :referrals, the
:referralsis 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
WHERESQL 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!
Posted by Flatiron School / November 7, 2013
Learn to Code Python: Free Lesson for Beginners
Sabrina Hernandez: From Dental Tech to UX / UI Product Designer
After 7 years as a dental tech, Sabrina Hernandez was ready for a change. She enrolled in Flatiron School and has since pivoted into a career in UX / UI Product Design.