How to do retrieve database records with multiple joins?

I'm not sure if this is a rails question or sql. Anyway...

I have three models: book, author, publisher
I have has_and_belongs_to_many relationships between book and author
and between book and publisher, so tables:
authors_books and books_publishers

Now, I want to find books that match a particular author(s) and also a
particular publisher(s).

Can someone shed some light?

I found a couple of articles that are close but aren't exactly what
I'm trying to do.
http://m.onkey.org/2007/11/1/find-users-with-at-least-n-items
http://blog.hasmanythrough.com/2006/6/12/when-associations-arent-enough

So, publisher <-> book <-> author

becomes

class Book < ActiveRecord::Base
has_and_belongs_to_many :publishers
has_and_belongs_to_many :authors

def self.find_with_authors_and_publishers(id)
  find(id, :include => [:authors, :publishers])
end
end

book = Book.find_with_authors_and_publishers(1)
book.authors # => array of authors
book.publishers # => array of publishers

note that the include is what creates the joins and reduces it all to
one query.

if you do not use the include it will still work but will make three
separate queries but will not query again but will pull the results from
a cache because the authors/publishers methods are singletons. the
process created by the include is referred to as eager loading.

So my understanding is that the include takes the 3 tables and makes
it into one table. So I tried the following in the console:

books = Book.find(:all, :include => [:authors, :publishers])
books # => array of books

Now, shouldn't books[0] have the attributes - author_id and
publisher_id?

To follow that up, if I wanted to find books that have an author_id of
3 or 5 and a publisher_id of 2, how would I write that?

Thanks for your help.

So my understanding is that the include takes the 3 tables and makes
it into one table. So I tried the following in the console:

books = Book.find(:all, :include => [:authors, :publishers])
books # => array of books

It doesnt. It means that books[12].authors won't hit the database
because it was all looked up in one go.

Fred

Does Book.find_by_author_and_publisher(@author_id, @publisher_id) work?

eggman2001 wrote:

So my understanding is that the include takes the 3 tables and makes
it into one table. So I tried the following in the console:

books = Book.find(:all, :include => [:authors, :publishers])
books # => array of books

class Book < ActiveRecord::Base

  def self.find_all_by_author_and_publisher(author, publisher)
    q =<<-END
      select b.* from books b
      join books_publishers bp on bp.book_id = b.id
      join author_books ab on ab.book_id = b.id
      where bp.publisher_id = #{publisher.id}
      and ab.author_id = #{author.id}
    END
    find_by_sql(q)
  end
end

NOT TESTED.. salt to taste..

ActiveRecord simply rules the roost but sometimes you are quicker to
just get your hands dirty for the sake of simplicity and speed..

(Loading all books in your db as you suggested earlier is akin to
suicide and you seem like a really nice guy so please reconsider)

Eager loading makes perfect sense in instances where you KNOW you will
be using the associations.. If you just need the books then don't bother
with it.. (Beware of premature optimization and overpriced consultants)

hth

ilan

Mark Wilden wrote:

Rats. I was hoping that Rails would transform that, but I guess that
would be too much.

If you wanted something really simple, you could do

        Book.find_by_author_id(@author_id) &
Book.find_by_publisher_id(@publisher_id)

///ark

I didn't look at the beginning of this thread, but I suspect that you want

     Book.find_all_by_...
              ^^^^^

if you expect there to be more than one. (Otherwise you get the first match)

-Rob

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

Mark Wilden wrote:

Book.find_by_author_id(@author_id) &
Book.find_by_publisher_id(@publisher_id)

That wouldn't work either

No, I'm pretty sure it works just fine. But you mean it wouldn't be
performant.

as you would be left with a lot of book on
both sides and then would have to find the intersection between the 2
sets which could be quite costly depending upon the number of books,
authors, and publishers within the dataset.

It _could_ be costly, but is it, for this application?

There are many solutions to this particular problem but only a few of them scale appropriately

Do we know this has to scale?

Seriously, of course I understand your objections (and of course I
understand the relative inefficiency of my suggestion), but when in
doubt, I just do the simplest thing that could possibly work and move
on. Whether the intersection thing "could possibly work" is a question
for the OP. I don't think it rates an automatic "this won't work."

At any rate, it was an idea, not necessarily a recommendation. :slight_smile:

///ark

That doesn't work (I tried it). My understanding is that the
find_by_<attribute> dynamic finder only works if that attribute is in
the Model you're calling it on, and in table relationships, the
attribute exists only in the belongs_to side of a has_xxx
relationship.

  def self.find_all_by_author_and_publisher(author, publisher)
    q =<<-END
      select b.* from books b
      join books_publishers bp on bp.book_id = b.id
      join author_books ab on ab.book_id = b.id
      where bp.publisher_id = #{publisher.id}
      and ab.author_id = #{author.id}
    END
    find_by_sql(q)
  end
end

That works, thank you.

Now, what about a situations where a user can choose multiple values
for each attribute? For example, you list every author in the Author
table and the user can select which ones he/she is interested in. The
user can choose 1 author, 5 authors or all of them.

You're right - sorry about that. I tried it too, but I mistakenly
tried it on a belongs_to model. And Rob's right, too that it has to be
find_all_xxx.

As Emily Litella would say, "Never mind." :slight_smile:

///ark

Now, what about a situations where a user can choose multiple values
for each attribute? For example, you list every author in the Author
table and the user can select which ones he/she is interested in. The
user can choose 1 author, 5 authors or all of them.

I've figured this out. Just to modify IIan's code:

class Book < ActiveRecord::Base

  def self.find_all_by_author_and_publisher(author, publisher)
    q =<<-END
      select b.* from books b
      join books_publishers bp on bp.book_id = b.id
      join author_books ab on ab.book_id = b.id
      where bp.publisher_id IN (#{publisher == 'all' ? 'publisher_id':
publisher})
      and ab.author_id IN (#{author == 'all' ? 'author_id': author})
    END
    find_by_sql(q)
  end
end

Now both arguments (author and publisher) will accept a string of the
form '1, 3, 5....' (and can contain little or many numbers as you
want) to find the matching rows and also take the string 'all', which
will effectively disregard that argument.

I wonder if it would be worth making this more ActiveRecordy by using
Book.find_all with :conditions, and :join. The nice thing about that
would be you could construct proper where clauses instead of using the
author_id = author_id trick. I'd actually proposed the latter as a
solution to another problem a while ago, but someone posted the ARy
solution and it might have advantages.

///ark

I wonder if it would be worth making this more ActiveRecordy by using
Book.find_all with :conditions, and :join. The nice thing about that
would be you could construct proper where clauses instead of using the
author_id = author_id trick. I'd actually proposed the latter as a
solution to another problem a while ago, but someone posted the ARy
solution and it might have advantages.

///ark

How would you re-write it? I'm not aware of alternatives to the
author_id = author_id trick.

You just construct your :conditions clause programmatically. If 'all'
is desired, then you don't even have a condition for that part.
Another nice thing about using :conditions gives you some niceties,
such as generating IN (...) when the condition contains an array.

///ark