Topic: Loading many to many :through in a single query

Hello all,

I'm having a bit of a performance bottleneck with some of my queries. All of them are related to a many-to-many :through relationship which is essentially like the one below:

class Book < ActiveRecord::Base
  has_many :authorships
  has_many :authors, :through => :authorships, :source => :person
end

class Authorship < ActiveRecord::Base
  belongs_to :book
  belongs_to :person
end

class People < ActiveRecord::Base
  has_many :authorships
  has_many :books, :through => :authorships
end


Usually, when I want to show a book I new all it related data, that is, its authors and authorships (authorships contain some additional information). Say I want to load all the books:

Book.find(:all, :include => [{:authorships => :person}, :authors])

This runs in a single query. Now if I want to load all the books (with all the info) by a single person, I can't find a way to do it in a single query. I have tried this:

peter.books.find(:all, :include => [{:authorships => :person}, :authorships])

and other similar :include options, but to no avail. This one loads the books and authorships in one single query, but then issues another query to retrieve the authors for each book on demand.

Is there any way to load all this on a single query? Or perhaps there is a better way rather than loading the books wholly?

Thanks in advance.

Re: Loading many to many :through in a single query

Have you tried just including :authors?

Book.find(:all, :include => :authors)
peter.books.find(:all, :include => :authors)

If this doesn't work in the second example, it may have something to do with attempting to join the people table twice. Try this instead:

Book.find(:all, :include => :authors, :conditions => ["authorships.person_id = ?", peter.id])

Railscasts - Free Ruby on Rails Screencasts

Re: Loading many to many :through in a single query

Thanks for your reply.

I just noticed that I made a mistake in the second query. It should be:

peter.books.find(:all, :include => [{:authorships => :person}, :authors])

This one runs in a single query. I also noticed that this one:

peter.books.find(:all, :include => [:authorships, :authors])

runs a bit faster than the first one. I can't remember why I started using this {:through_table => :related_table} idiom, but I'll have to check if taking it out improves other queries.