Complex associations in ActiveRecord models

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!

From stackoverflow

• 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.

• 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
  

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

• 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