Retrieving Entries from Grandchildren's Table

Question

My question is related to Rails, but even a generic SQL answer would be helpful.

I deal with four tables: categories, books, categories and books. The category has and belongs to many books. Books have many recipes.

In translating Rails code, I:

class Category < ActiveRecord::Base has_and_belongs_to_many :books end class Book < ActiveRecord::Base has_and_belongs_to_many :categories has_many :recipes end class Recipe < ActiveRecord::Base belongs_to :book end 

I am trying to get all the recipes contained in books in this category.

I know how to do this with many queries, but not with one query. With many requests, I would do:

 recipes = [] books = @category.books books.each do |book| recipes << book.recipes.flatten end 

I do not like this because it requires N + 1 queries. I probably need to join in to do everything at once, but I'm not sure of the syntax in ActiveRecord or SQL. I am using MySQL.

Answer 1

Cannot help with Ruby. Generic SQL answer (with some assumptions about column names):

 SELECT Recipe.* FROM Recipe INNER JOIN Book ON Recipe.Book = Book.Id INNER JOIN Category ON Book.Category = Category.Id WHERE Category.Id = ? 

EDIT . Another version of the query has been added to match the new Pinky Brain table and column information. Something like that?

 SELECT Recipe.* FROM Recipe INNER JOIN Categories_Books ON Categories_Books.book_id = Recipe.book_id WHERE Categories_Books.category_id = ? 

Answer 2

Assuming the following table structure ...

 category( category_id primary key ) book( book_id primary key ) recipes( recipe_id ,book_id ,primary key(recipe_id) ) categories_books( category_id ,book_id ,primary key(category_id, book_id) ) 

... you can get what you want with the following query, which returns all recipients from all books in the specified category.

 select ... from categories_books c join book b using(book_id) join recipes r using(book_id) where c.category_id = ?; 

Please note that you can delete the connection in the book if you do not need data from the table, since you can directly join from categories_books to recipes via book_id . Also, make sure recipes.book_id

there is an index (not unique)