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. 
///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.”
///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