One lookup table for two different joins

Hello Folks,

I'm trying to set up a Band model that uses one lookup table (Instruments) to describe two things:

1. Instruments that the Band currently plays 2. Instruments that the Band needs

I only want to have one Instruments table to describe all instruments for maintainability. Is there a way to do this? Here is my current Band model:

class Band < ActiveRecord::Base   attr_accessible :name, :description, :genre_ids, :instrument_ids

  # Band Instruments played   has_many :band_plays_instruments   has_many :instruments, :through => :band_plays_instruments

  # Band Instruments needed   has_many :band_instrument_needs   has_many :instruments, :through => :band_instrument_needs


The problem is that apparently I can't have two "has_many :instruments, :through => x" statements in the model. Does anybody have any recommendations as to how I should handle this?

Thank you in advance!

One table for instruments is the right idea, but you would probably be better off then having separate join-tables for "instruments_wanted" and "instuments_played". This would give you the functionality of having extra attributes dedicated to those collections: an owned instrument might have a record of which band members play it; desired instruments might have a price that needs to be paid to buy it.

class Band < ActiveRecord::Base        has_many :instruments_played        has_many :instruments_needed end

class InstrumentsPlayed < ActiveRecord::Base   belongs_to :instrument end

class InstrumentsNeeded < ActiveRecord::Base   belongs_to :instrument end

(of course, you might want to fiddle with the :class_name and table names as these aren't intuitively pluralized - or just go with "instruments_neededs" :slight_smile:

in your code you can now use:   @band.instruments_played.each do |instrument_played|     instrument_played.instrument ....

and   @band.instruments_played << InstrumentPlayed.create(:instrument => Instrument.find_by_name("bassoon"))

Or you can add shortcuts to just the instruments (should you need) to the band model:   def band_plays_instruments     instruments_played.inject() { |instrument_played| instruments << instrument_played.instrument }   end