Rails - Jan 2008 - 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 Pratt
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> 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.
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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?
>
>
> 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 Pratt
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
>> 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.
>> --
>> Posted viahttp://www.ruby-forum.com/.
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 16, 8:35 pm, eggman2001
<sod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Mark Wilden wrote:
> On Jan 16, 8:35�pm, eggman2001
<sod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 17, 9:59 am, Ilan Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Mark Wilden 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 17, 2008, at 2:14 PM, Mark Wilden wrote:
>
> On Jan 17, 9:59 am, Ilan Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
>
>> Mark Wilden 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

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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Mark Wilden wrote:
> On Jan 17, 9:59�am, Ilan Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
> 
>> Mark Wilden 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



-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 17, 11:24 am, Ilan Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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. :)

///ark
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 17, 4:07 pm, Mark Wilden
<m...-OCn100epQuBBDgjK7y7TUQ@public.gmane.org>
wrote:
> On Jan 17, 11:24 am, Ilan Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> 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_<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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

>   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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 17, 5:56 pm, eggman2001
<sod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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." :)

///ark
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 17, 8:45 pm, eggman2001
<sod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jan 18, 1:39 pm, eggman2001
<sod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---