Rails HABTM joining with another condition

I am trying to get a list, and I will use books as an example.

class Book < ActiveRecord::Base
  belongs_to  :type
  has_and_belongs_to_many   :genres
end

class Genre < ActiveRecord::Base
  has_and_belongs_to_many   :books
end

So in this example I want to show a list of all Genres, but it the first column should be the type. So, if say a genre is "Space", the types could be "Non-fiction" and "Fiction", and 开发者_运维百科it would show:

Type          Genre    
Fiction       Space
Non-fiction   Space

The Genre table has only "id", "name", and "description", the join table genres_books has "genre_id" and "book_id", and the Book table has "type_id" and "id". I am having trouble getting this to work however.

I know the sql code I would need which would be:

SELECT distinct genres.name, books.type_id FROM `genres` INNER JOIN genres_books ON genres.id = genres_books.genre_id INNER JOIN books ON genres_books.book_id = books.id order by genres.name

and I found I could do

@genre = Genre.all
@genre.each do |genre|
  @type = genre.book.find(:all, :select => 'type_id', :group => 'type_id')
  @type.each do |type|

and this would let me see the type along with each genre and print them out, but I couldn't really work with them all at once. I think what would be ideal is if at the Genre.all statement I could somehow group them there so I can keep the genre/type combinations together and work with them further down the road. I was trying to do something along the lines of:

 @genres = Genre.find(:all, :include => :books, :select => 'DISTINCT genres.name, genres.description, books.product_id', :conditions => [Genre.book_id = :books.id, Book.genres.id = :genres.id] )

But at this point I am running around in circles and not getting anywhere. Do I need to be using has_many :through?

---

The following examples use your models, defined above. You should use scopes to push associations back into the model (alternately you can just define class methods on the model). This helps keep your record-fetching calls in check and helps you stick within the Law of Demeter.

Get a list of Books, eagerly loading each book's Type and Genres, without conditions:

def Book < ActiveRecord::Base
  scope :with_types_and_genres, include(:type, :genres)
end

@books = Book.with_types_and_genres  #=> [ * a bunch of book objects * ]

Once you have that, if I understand your goal, you can just do some in-Ruby grouping to corral your Books into the structure that you need to pass to your view.

@books_by_type = @books.group_by { |book| book.type }

# or the same line, more concisely
@books_by_type = @books.group_by &:type

@books_by_type.each_pair do |type, book|
  puts "#{book.genre.name} by #{book.author} (#{type.name})"
end