Forum: Ruby on Rails habtm "AND" find conditions

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
342549fb649ecab2f193448eaf9eb44c?d=identicon&s=25 John R. (jjrussell)
on 2006-06-07 13:20
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.
Brian Hughes (Guest)
on 2006-06-07 17:05
(Received via mailing list)
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
342549fb649ecab2f193448eaf9eb44c?d=identicon&s=25 John R. (jjrussell)
on 2006-06-07 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?
B5993386abda30bd233b24e8891beb31?d=identicon&s=25 Rodney R. (rodney)
on 2006-06-07 20:17
(Received via mailing list)
John , what you want makes sense , it's just done a bit differently :-)

check out

http://blog.hasmanythrough.com/articles/2006/02/28...

Josh explains things nicely.


--
Rodney
http://www.pinupgeek.com
http://www.dutchrailers.org
342549fb649ecab2f193448eaf9eb44c?d=identicon&s=25 John R. (jjrussell)
on 2006-06-08 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...
>
> 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.
B5993386abda30bd233b24e8891beb31?d=identicon&s=25 Rodney R. (rodney)
on 2006-06-08 19:59
(Received via mailing list)
> 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
Isak Hansen (Guest)
on 2006-06-09 01:40
(Received via mailing list)
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
342549fb649ecab2f193448eaf9eb44c?d=identicon&s=25 John R. (jjrussell)
on 2006-06-09 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.
Ec5a090a768bf580fffc291aabb133a6?d=identicon&s=25 Josh S. (jsusser)
on 2006-06-09 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
342549fb649ecab2f193448eaf9eb44c?d=identicon&s=25 John R. (jjrussell)
on 2006-06-09 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.
3faa396720ce6ab2cec6e7638559a3b4?d=identicon&s=25 Dustin F. (dustin)
on 2007-05-02 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?
This topic is locked and can not be replied to.