clog

Joins in SQL queries are notorious for breaking code. If you have to modify how your tables are associated in any way, you’ll most likely have to revisit any joins you’ve written to keep your code from breaking. In a large system this can be A LOT of joins. Additionally, writing queries with multiple joins can be very cryptic and confusing. It’s easy to make mistakes and difficult to see the relationships by looking at the queries.

Rails 1.1 introduced some awesome features that virtually eliminate joins. The has_one, has_many, belongs_to, and has_and_belongs_to_many methods provide a bulk of this.

Unfortunately for me (in a way), I learned Rails in the pre-1.0 era and then migrated my company at 1.0. I was stuck there for some time and got behind on the 1.1 improvements. While catching up, I ran across some things that aren’t so well documented and very handy.

One such technique I came across should be documented. So here it is (mostly for my own good).

In this instance I need a true join table rather than has_and_belongs_to_many. Say you have a relationship structure defined below:

class A < Base
  has_many :B
  #relationship to D via the join table (B)
  has_many :D,  :through => :B
end

class B < Base
  belongs_to :A
  belongs_to :D
  has_many :C
end

class C < Base
  belongs_to :B
end

class D < Base
  belongs_to :B
end

You are searching on class A and want to join all of the matching rows from both B and C. This is a nested has_many relationship (meaning nested joins). How do you do this? It’s not so obvious, but it’s beautiful once you learn. All you need to do to stack up has_many relationships is to stack up (embed) Hashes that get sent to the include argument of the find call. Rails will recursively extract each of these tables and create the nested relationships for you!

This is it… no more ugly nested joins!

A.find(:all, :include => {:B => :C})

There may yet be instances where you need joins, but the occurrences are fewer and fewer these days. The hardest thing now is going to be how to remember SQL. It’s actually easier to do test queries in the console now!