Complex associations in ActiveRecord models

Question

I'm trying to understand how ActiveRecord deals with associations that are more complex than simple has_many, belongs_to, and so on.

As an example, consider an application for recording music gigs. Each Gig has a Band, which has a Genre. Each Gig also has a Venue, which has a Region.

In the rough notation of MS Access (which I'm suddenly beginning to feel quite nostalgic for) these relationships would be presented like this

      1  ∞      1  ∞     ∞  1       ∞  1
Genre ---- Band ---- Gig ---- Venue ---- Region

I would like to be able to find out, for example, all the bands who've played in a region, or all the venues that host a certain genre.

Ideally, my models would contain this code

class Genre
  has_many :bands
  has_many :gigs, :through => bands
  has_many :venues, :through => :gigs, :uniq => true
  has_many :regions, :through => :venues, :uniq => true
end

class Band
  belongs_to :genre
  has_many :gigs
  has_many :venues, :through => :gigs, :uniq => true
  has_many :regions, :through => :venues, :uniq => true
end

class Gig
  belongs_to :genre, :through => :band
  belongs_to :band
  belongs_to :venue
  belongs_to :region, :through => :venue
end

and so on for Venue and Region.

However, it seems I have to produce something like this instead

class Genre
  has_many :bands
  has_many :gigs, :through => bands
  has_many :venues, :finder_sql => "SELECT DISTINCT venues.* FROM venues " +
    "INNER JOIN gigs ON venue.id = gig.venue_id " +
    "INNER JOIN bands ON band.id = gig.band_id " +
    "WHERE band.genre_id = #{id}"
  # something even yuckier for regions
end

class Band
  belongs_to :genre
  has_many :gigs
  has_many :venues, :through => :gigs, :uniq => true
  # some more sql for regions
end

class Gig
  delegate :genre, :to => :band
  belongs_to :band
  belongs_to :venue
  delegate :region, :to => :venue
end

I have two questions - one general and one particular.

The general:

I would have thought that what I was trying to do would come up fairly often. Is what I have really the best way to do it, or is there something much simpler that I'm overlooking?

The particular:

What I have above doesn't actually quite work! The #{id} in the second genre model actually to return the id of the class. (I think). However, this seems to work here and here

I realise this is a rather epic question, so thank you if you've got this far. Any help would be greatly appreciated!

Answer 1

For associations like this, you're going to end up writing custom SQL -- there's no real way that you can handle a chain of associations like this without having to do some fairly massive joins, and there really isn't an efficient way for the built-in query generators to handle it with a one-liner.

You can look into the :joins parameter of ActiveRecord as well -- this may do what you want.

Answer 2

Associations are designed to be readable and writable. A large part of their value is that you can do something like this:

@band.gigs << Gig.new(:venue => @venue)

It sounds, though, like you want something that's read-only. In other words, you want to associate Venues and Genres, but you'd never do:

@venue.genres << Genre.new("post-punk")

because it wouldn't make sense. A Venue only has a Genre if a Band with that particular Genre has a Gig there.

Associations don't work for that because they have to be writable. Here's how I'd do readonly associations:

class Genre
  has_many :bands 

  def gigs
    Gig.find(:all, :include => 'bands', 
             :conditions => ["band.genre_id = ?", self.id])
  end

  def venues 
    Venue.find(:all, :include => {:gigs => :band}, 
      :conditions => ["band.genre_id = ?", self.id])
  end
end

Answer 3

Sounds like a job for nested_has_many_through! Great plugin that allows you to do nested has_many :throughs

Answer 4

You can add conditions and parameters to your associations. Recent versions of ActiveRecord give the power of named_scopes, which will work on associated records as well.

From a current project

Folder has_many Pages
Page has_many Comments

# In Page
named_scope :commented,
  :include => "comments", 
  :conditions => ["comments.id IS NULL OR comments.id IS NOT NULL"], 
  :order => "comments.created_at DESC, pages.created_at DESC"

Using this we can say:

folder.pages.commented

Which will scope on the associated records, doing a conditional with the supplied parameters.

Plus! named_scopes are composable.

And more scopes:

named_scope :published, :conditions => ["forum_topics.status = ?", "published"]

And chain them together: folder.pages.published.commented