0

I have 2 models with a many-to-many relationship (simplified here with books-authors example):

class Book < ActiveRecord::Base  
    has_and_belongs_to_many :authors
end

class Author < ActiveRecord::Base
    has_and_belongs_to_many :books
end

I need to display a list of books, and under each one a list of its authors. Users are able to filter via a certain author.

I'm trying to filter via an author in the following way:
Let's assume there's only one resulting book, and it has 3 authors.

books = Book.find(:all, :include => :authors, :conditions => "authors.id = 5")

When I try to list the different authors of the books retrieved, I get only the author with ID=5.

books.first.authors.size   # => 1
books.first.authors.first.id   # => 5

I would expect rails to go and retrieve all the different authors associated with the books, either during the find query or as a follow-up query, yet it does not.

How can I solve this problem? Is there something wrong with the associations? the find query?

Thanks.

Nadav
  • 119
  • 1
  • 1
  • 8

1 Answers1

0

This happens because you use :include in your find method. When you do that, you tell Rails to cache the associated result (eager loading) to avoid multiple database queries. And since the conditions you have specified only includes one of the three authors, that is the only one that will be cached and then looped through when you call .authors

I think the easiest solution would be to change :include to :joins. It will perform the same database query, but it will not cache the associated authors. So when you call authors for each book, it will perform a new database call to retrieve all authors for that book. As I said, the easiest but perhaps not the best since it could potentially result in a lot of queries to the database.

Here is another way you could do it:

book_ids = Book.all(:joins => :authors, 
                    :conditions => ["authors.id = ?", 5]).map(&:id)
books = Book.all(:include => :authors, 
                 :conditions => ["books.id IN (?)", book_ids])

This will first collect all id's of the books related to a specific author. Then it will query the database again with the resulting book_ids and this time include all authors related to those books and cache it to avoid unnecessary db calls.

Edit:

If you really want to use only one call, then I think the only options is to write a little more SQL in the call. Perhaps like this:

books = Book.all(:include => :authors, 
                 :conditions => ["books.id IN (SELECT book_id FROM authors_books WHERE author_id = ?)", 5])
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DanneManne
  • 21,107
  • 5
  • 57
  • 58
  • Thanks, I've thought about this solution, but I was hoping there's a simpler solution. Regarding joins: when I use joins in this example, I would get an array of 3 books which are the same book, each one having a different author, so it doesn't solve the problem... – Nadav Dec 15 '10 at 09:32
  • Okay, when you say this example, I assume you mean when you just change :include to :joins in your described example. Because I verified the code I supplied last and it worked for me. – DanneManne Dec 15 '10 at 09:56
  • Yes, your code works, but I don't want to break it in the code to 2 different queries. Isn't there a way to run a single rails query without fetching the ids manually and then fetching the objects themselves? – Nadav Dec 15 '10 at 12:46
  • Okey, edited my answer with an additional scenario. The only other way I can think of right now. – DanneManne Dec 15 '10 at 12:59
  • Which Rails version is this? > "I would get an array of 3 books which are the same book, each one having a different author, so it doesn't solve the problem" This is incorrect. ActiveRecord will correctly give you the one book returned by the query. Don't depend on what it prints in the console. Try an enumerator method. – Swanand Dec 15 '10 at 13:41
  • I didn't depend on the console, I was printing via each method to the screen, and that was what I got.. – Nadav Dec 16 '10 at 08:46