Is there a better way to join these tables in ActiveRecord from bottom to top relation?

Question

Author has_many Books

Books has_many Content

If I want to find some duplicate section , for example: "interest" , in all books and all authors . And I would also like to see their book.title and author.name.

content_section = Content.where(:section  => "interest").select("book_id, section ";

books = Books.where(:id => content_section.map(&:book_id)).select("author_id, title")

authors = Author.where(:id => books.map(&:author_id)).select("name")

Question1: Is there a better way to achieve this ? ( in performance issue or some others )
Question2: Is there a way to make content_section, books, and authors to merge into a table ? (in ruby side or MySQL side.)
Question3: what is content_section.map(&:book_id) means? I wonder the means of &:book_id

for example, this SQL(MySQL) will make a big table , so I don't need to merge all result in ruby. And it's easier for me to iterate in views template. But it's performance is very low since joined three table.

select * from authors, books, contents 

where authors.id = books.author_id and books.id = content.book_id and content.section = "interest"

Any comment is appreciated. Thanks.

** update **

after referenced: enter link description here and enter link description here

I try to add model :

class Author

has_many: content , :through => book

end

then I tried this in console:

author.joins( :content ).where(:section => "interest").select("name, title, section:);

I get a SQL :

select name, title, section from author inner join books on .... inner join on content .......

Is this ok ? thanks.

The performancd of that query (or its activerecord equivalent) will depend on what indexes you have on those tables. What indexes do you have? — Frederick Cheung, Feb 27 '12 at 08:29
Now I've already added 'section' index. But I can add all index just if needed. thanks. — hey mike, Feb 27 '12 at 08:45
Also worth checking the output of explain to see what your query is doing — Frederick Cheung, Feb 27 '12 at 08:51

Tom L · Accepted Answer · 2012-04-17T18:10:35.853

This might do it:

class Author
  has_many :books
  has_many :contents, :through => :books

  scope :by_section, lambda { |section| joins(:contents).where('contents.section' => section) }
end

Then you can call:

authors = Author.by_section("interest")

Also, you were wondering about content_sections.map(&:book_id). That's shorthand for content_sections.map { |content_section| section.book_id } which results in an array of book ids.

Is there a better way to join these tables in ActiveRecord from bottom to top relation?

1 Answers1