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 07.06.2006 13:20
on 07.06.2006 17:05
On Jun 7, 2006, at 07:20 AM, John Russell 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
on 07.06.2006 18:27
Brian Hughes wrote: > On Jun 7, 2006, at 07:20 AM, John Russell 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?
on 07.06.2006 20:17
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. -- Rodney http://www.pinupgeek.com http://www.dutchrailers.org
on 08.06.2006 15:34
Rodney Ramdas 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.
on 08.06.2006 19:59
> 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 > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > -- Rodney http://www.pinupgeek.com http://www.dutchrailers.org
on 09.06.2006 01:40
On 6/7/06, John Russell <jjrussell@gmail.com> 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
on 09.06.2006 02:01
Isak Hansen wrote: > On 6/7/06, John Russell <jjrussell@gmail.com> 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.
on 09.06.2006 08:13
John Russell 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 Susser http://blog.hasmanythrough.com
on 09.06.2006 13:26
Josh Susser 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 02.05.2007 00:45
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?