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.

On Jan 17, 1:46 am, Keynan P. [email protected]

On 17 Jan 2008, at 16:57, 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

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

Fred

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

On Jan 16, 8:35 pm, eggman2001 [email protected] wrote:

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).

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 W. wrote:

On Jan 16, 8:35�pm, eggman2001 [email protected] wrote:

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).

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

Not in this case, the above only works if author_id and publisher_id are
located in book (1 to many)… in this example we have a many to many
which necessitates going through the join(or link) tables…

hth…

ilan

On Jan 17, 9:59 am, Ilan B. [email protected]
wrote:

Mark W. wrote:

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

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

On Jan 17, 2008, at 2:14 PM, Mark W. wrote:

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 B. http://agileconsultingllc.com
[email protected]

Mark W. wrote:

On Jan 17, 9:59�am, Ilan B. [email protected]
wrote:

Mark W. wrote:

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

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

That wouldn’t work either 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… There are many solutions
to this particular problem but only a few of them scale appropriately

ilan

On Jan 17, 11:24 am, Ilan B. [email protected]
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

On Jan 17, 4:07 pm, Mark W. [email protected] wrote:

On Jan 17, 11:24 am, Ilan B. [email protected]
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.

That doesn’t work (I tried it). My understanding is that the
find_by_ 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.

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.

On Jan 17, 5:56 pm, eggman2001 [email protected] 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.

That doesn’t work (I tried it).

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.

On Jan 17, 8:45 pm, eggman2001 [email protected] wrote:

  and ab.author_id IN (#{author == 'all' ? 'author_id': author})
END
find_by_sql(q)

end
end

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.

On Jan 18, 1:39 pm, eggman2001 [email protected] wrote:

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs