Complicted ActiveRecord Association. Going through a 4th table

Question

I have kind of a complicated case and am wondering how this would work in rails:

I want to categories the genres of some singers. Singers can belong to more than one genres, and users can assign tags to each genre

For example:

singers  <-- singers_genres --> genres <-- genres_tags --> tags

SQL would look something like:

SELECT * FROM singers S 
     INNER JOIN singers_genres SG ON S.id=SG.singer_id
     INNER JOIN genres G ON G.id = SG.genre_id
     LEFT OUTER JOIN genre_tags GT ON G.id = GT.genre_id
     INNER JOIN tags T ON GT.tag_id = T.id

Here are what my Classes look like:

class Singer
   has_and_belongs_to_many :genres, :include => :tag

class Genre
   has_and_belongs_to_many :singers
   has_and_belongs_to_many :tags

class Tag
   has_and_belongs_to_many :genres

Answer 1

Choosing Between has_many :through and has_and_belongs_to_many is a good introduction to associations.

Maybe you can post your models here to see how the associations are designed.

Answer 2

Let's create the project ...

rails itunes
cd itunes

create the basic models:

script/generate model Singer name:string
script/generate model Genre name:string
script/generate model Tag name:string

do the migration:

rake db:migrate

update the models:

class Singer < ActiveRecord::Base
  has_and_belongs_to_many :genres
end

class Genre < ActiveRecord::Base
  has_and_belongs_to_many :singers
  has_and_belongs_to_many :tags
end

class Tag < ActiveRecord::Base
  has_and_belongs_to_many :genres
end

create two more migration for joining tables:

script/generate migration CreateGenresSingersJoin
script/generate migration CreateGenresTagsJoin
rake db:migrate

the genres_singers model:

class CreateGenresSingersJoin < ActiveRecord::Migration
  create_table 'genres_singers', :id => false do |t|
    t.integer 'genre_id'
    t.integer 'singer_id'
  end

  def self.down
    drop_table'genres_singers'
  end
end

the genres_tags model:

class CreateGenresTagsJoin < ActiveRecord::Migration
  create_table 'genres_tags', :id => false do |t|
    t.integer 'genre_id'
    t.integer 'tag_id'
  end

  def self.down
    drop_table'genres_tags'
  end
end

create some seeding data in seeds.db, or whatever means:

Singer.create(:name => 'Lady Ga Ga')
Genre.create(:name => 'Pop')
Genre.create(:name => 'Folk')
Tag.create(:name => 'Top50')

insert some link data:

INSERT INTO genres_singers (genre_id, singer_id) VALUES (1, 1)
INSERT INTO genres_singers (genre_id, singer_id) VALUES (2, 1)
INSERT INTO genres_tags (genre_id, tag_id) VALUES (1, 1)

then we can use the associations, e.g.:

Singer.first.genres.first.tags.first
=> #<Tag id: 1, name: "Top50">

Singer.find_by_name("Lady Ga Ga").genres.first.tags
=> [#<Tag id: 1, name: "Top50">]