I have two tables and a join table for them
e.g. books, authors in a many to many relationship (habtm) and a join
table books_authors.
I can successfully search for a book that has
“author.id = 2 OR author.id = 4”
but I am unable to search for
“author.id = 2 AND author.id = 4”
This is because the result of all the joins only has one author.id
column so no single row has “id = 2 AND id = 4” even if different rows
have the same book.id. Also I would like to be able to search for any
number of author.id’s
e.g.
“author.id = 2 AND author.id = 4 AND author.id = 6”
would return a book that has three authors of id 2,4 and 6. etc
How is this possible? Thanks for the help.
On Jun 7, 2006, at 07:20 AM, John R. wrote:
Also I would like to be able to search for any
number of author.id’s
e.g.
“author.id = 2 AND author.id = 4 AND author.id = 6”
would return a book that has three authors of id 2,4 and 6. etc
How is this possible? Thanks for the help.
I think you are approaching it from the wrong side of the
relationship. If you want to find all the books that are associated
with multiple authors, don’t go from the Book side, go from the
Author side:
Author.find([2, 4, 6], :include => :books)
-Brian
Brian H. wrote:
On Jun 7, 2006, at 07:20 AM, John R. wrote:
Also I would like to be able to search for any
number of author.id’s
e.g.
“author.id = 2 AND author.id = 4 AND author.id = 6”
would return a book that has three authors of id 2,4 and 6. etc
How is this possible? Thanks for the help.
I think you are approaching it from the wrong side of the
relationship. If you want to find all the books that are associated
with multiple authors, don’t go from the Book side, go from the
Author side:
Author.find([2, 4, 6], :include => :books)
Thanks a lot for your response. I tried your suggestion and what I got
was an array of author objects, each of which had an array of book
objects that matched that author. What I need is an array of the books
that have both of those authors. The way it is here there is no way to
get a single list of books that match both authors. I mean, I suppose I
could grind through the two arrays and remove dups, but that’s not very
… rails.
Also, if I reverse the direction of the relationship I lose the ability
to have additional conditions associated with the books.
e.g., I want a list of books that have author.id = 1 AND author.id = 4
AND is blue AND has a title LIKE “%amalgam%” AND is 8 inches tall.
Does that make sense?
Rodney R. wrote:
John , what you want makes sense , it’s just done a bit differently 
check out
http://blog.hasmanythrough.com/articles/2006/02/28/association-goodness
Josh explains things nicely.
I am actually using this for another many to many relationship that I
have, but it doesn’t actually address the problem, which, the more I
think about it, is really a fundamental SQL one.
If I have two tables joined by a third, and then join them all together,
all columns from all tables show up one time in the join, so how could I
have an AND condition on any of them? How can author.id be both 2 and 3
at the same time?
But what i want is
books books_authors authors
1 1 | 1 1
2 1 | 2 2
3 2 | 1
3 | 2
How can I query to get just book 1 because it has BOTH authors and not 2
and 3 because they don’t have both.
have an AND condition on any of them? How can author.id be both 2 and 3
at the same time?
it can’t, the rule of physics apply
How can I query to get just book 1 because it has BOTH authors and not 2
and 3 because they don’t have both.
Josh does show that, but you have be a bit creative,
rename/rethink your books_authors to contributions, in your Book model
use has_many :authors , :through => :contributions and you can simply
do Author.find(1).books, giving all books by author 1
seriously
–
Posted via http://www.ruby-forum.com/.
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
–
Rodney
http://www.pinupgeek.com
http://www.dutchrailers.org
Isak H. wrote:
On 6/7/06, John R. [email protected] wrote:
snip
I mean, I suppose I
could grind through the two arrays and remove dups, but that’s not very
… rails.
How much data are we talking about here? “Book.find_by_author_id(1) &
Book.find_by_author_id(2)” obviously isn’t optimal, but if it works
“well enough”…?
I have no idea if there’s a ‘proper’ way to do what you want, but it
seems esoteric enough that my guess would be no.
However, Rails does give you the full power of SQL when you want it. I
probably use find_by_sql() too much myself, but here it’s a perfect
fit.
that’s just my point. I don’t think this can be done with sql either.
Its not just this piece of data that I need, its this in conjunction
with other conditions based on the book, e.g., title, size, pictures
etc. So I can’t just go the other way to satisfy just one condition, I
need to be able to search on all those other conditions, as well as this
one. The table diagram above is why i think I can’t even do this in
SQL. My fallback now is to try to run the query with just one of the
conditions in the m2m table and then use ruby to run through the
resulting objects and take out the ones that don’t match the remainder
of the conditions. This is just enourmously heinous.
I really appreciate all the help and comments.
John R. wrote:
that’s just my point. I don’t think this can be done with sql either.
Its not just this piece of data that I need, its this in conjunction
with other conditions based on the book, e.g., title, size, pictures
etc. So I can’t just go the other way to satisfy just one condition, I
need to be able to search on all those other conditions, as well as this
one. The table diagram above is why i think I can’t even do this in
SQL. My fallback now is to try to run the query with just one of the
conditions in the m2m table and then use ruby to run through the
resulting objects and take out the ones that don’t match the remainder
of the conditions. This is just enourmously heinous.
You can’t do it with the normal AR associations, but custom SQL will
work. You’ll need to generate SQL with a JOIN clause for each author of
the book. It’s just like joining to multiple tables, but you do it to
the same table multiple times. Then you use the WHERE clause to specify
the author_id values for each author.
SELECT books.* FROM books
INNER JOIN books_authors ba1 ON books.id = ba1.book_id
INNER JOIN books_authors ba2 ON books.id = ba2.book_id
WHERE ba1.author_id = 1 AND ba2.author_id = 2
Shove that in a find_by_sql and that should work. (I’m not a SQL god so
you might need to tweek the syntax.) You should even be able to generate
such a statement for an arbitrary number of authors pretty easily.
SQL is really good at this sort of stuff. It’s just non-standard cases
like this that make ActiveRecord faint.
–
Josh S.
http://blog.hasmanythrough.com
Josh S. wrote:
…
You can’t do it with the normal AR associations, but custom SQL will
work. You’ll need to generate SQL with a JOIN clause for each author of
the book. It’s just like joining to multiple tables, but you do it to
the same table multiple times. Then you use the WHERE clause to specify
the author_id values for each author.
SELECT books.* FROM books
INNER JOIN books_authors ba1 ON books.id = ba1.book_id
INNER JOIN books_authors ba2 ON books.id = ba2.book_id
WHERE ba1.author_id = 1 AND ba2.author_id = 2
Shove that in a find_by_sql and that should work. (I’m not a SQL god so
you might need to tweek the syntax.) You should even be able to generate
such a statement for an arbitrary number of authors pretty easily.
SQL is really good at this sort of stuff. It’s just non-standard cases
like this that make ActiveRecord faint.
Thanks for the query. Maybe I’ll try this with a find_by_sql or
something. Thanks a lot.
On 6/7/06, John R. [email protected] wrote:
snip
I mean, I suppose I
could grind through the two arrays and remove dups, but that’s not very
… rails.
How much data are we talking about here? “Book.find_by_author_id(1) &
Book.find_by_author_id(2)” obviously isn’t optimal, but if it works
“well enough”…?
I have no idea if there’s a ‘proper’ way to do what you want, but it
seems esoteric enough that my guess would be no.
However, Rails does give you the full power of SQL when you want it. I
probably use find_by_sql() too much myself, but here it’s a perfect
fit.
Isak
Thanks John,
The SQL code is way faster than using ‘&’ on the arrays of ActiveRecord
results.
I’m dealing with a similar issue. There are two tables:
LOCATIONS = [ID, Name…]
CATEGORIES = [ID, Value]
and a joining table:
CATEGORY_LOCATION = [Category_ID, Location_ID]
Each Location has and belongs to many Categories.
I want to be able to retrieve Locations by specifying some logical
combination of Categories.
I wrote some code to generate the SQL automatically, following your
model, and it worked fine until I added disjunctions (OR) and the
third INNER JOIN. After that, it took a long time to return the
results, where it had several duplicate records.
This works great, matching 9 records:
SELECT locations.* FROM locations
INNER JOIN category_location Spatial0_0 ON locations.id =
Spatial0_0.location_id
INNER JOIN category_location Spatial0_1 ON locations.id =
Spatial0_1.location_id
WHERE (Spatial0_0. category_id = 24 AND Spatial0_1. category_id = 70)
However this (below) takes a long time to retrieve the results and
retrieves 445 records, each unique one being repeated about 15 times (I
guess the OR is doing something different than I hoped).
SELECT locations.* FROM locations
INNER JOIN category_location Spatial0_0 ON locations.id =
Spatial0_0.location_id
INNER JOIN category_location Spatial0_1 ON locations.id =
Spatial0_1.location_id
INNER JOIN category_location Spatial1_0 ON locations.id =
Spatial1_0.location_id
WHERE (Spatial0_0. category_id = 24 AND Spatial0_1. category_id = 70) OR
(Spatial1_0.category_id = 214)
Any suggestions?