To further show an example of trying to do this, here is a 'real
world' example. However, in this particular case, I'm only showing two
features, but in real usage, a user might select as many as 50
features or so.
mysql> SELECT c.park_id, a.name, c.feature_id, b.name FROM parks a,
features b, features_parks c WHERE (c.park_id = a.id) AND
(c.feature_id = b.id) AND (c.feature_id = 21);
+---------+-------------------------+------------+--------+
| park_id | name | feature_id | name |
+---------+-------------------------+------------+--------+
| 3120 | Mitchell Park | 21 | Swings |
| 21385 | Boulware Park | 21 | Swings |
| 2351 | Bowden Park | 21 | Swings |
| 21561 | Cameron Park | 21 | Swings |
| 24791 | Ramos Park | 21 | Swings |
| 2585 | Eleanor Park | 21 | Swings |
| 2627 | Flood County Park | 21 | Swings |
| 2374 | Burgess Park | 21 | Swings |
| 22986 | Holbrook-Palmer Park | 21 | Swings |
| 2799 | Huddart Park | 21 | Swings |
| 3354 | Rinconada Park | 21 | Swings |
| 3693 | Werry Park | 21 | Swings |
| 26152 | Willow Oaks Park | 21 | Swings |
| 23559 | Live Oak Manor Park | 21 | Swings |
| 24295 | Oak Meadow Park | 21 | Swings |
| 25562 | Sunnyvale Baylands Park | 21 | Swings |
| 21354 | Cornelis Bol Park | 21 | Swings |
| 21459 | Bubb Park | 21 | Swings |
| 21910 | Cooper Park | 21 | Swings |
| 24859 | Rengstorff Park | 21 | Swings |
| 24043 | Monroe Mini Park | 21 | Swings |
| 24867 | Rex Manor Playground | 21 | Swings |
+---------+-------------------------+------------+--------+
22 rows in set (0.00 sec)
mysql> SELECT c.park_id, a.name, c.feature_id, b.name FROM parks a,
features b, features_parks c WHERE (c.park_id = a.id) AND
(c.feature_id = b.id) AND (c.feature_id = 51);
+---------+--------------------------+------------+---------+
| park_id | name | feature_id | name |
+---------+--------------------------+------------+---------+
| 3120 | Mitchell Park | 51 | Sandpit |
| 2351 | Bowden Park | 51 | Sandpit |
| 2374 | Burgess Park | 51 | Sandpit |
| 2474 | Coyote Point County Park | 51 | Sandpit |
| 24867 | Rex Manor Playground | 51 | Sandpit |
+---------+--------------------------+------------+---------+
5 rows in set (0.00 sec)
So if I was trying to fetch all parks that have BOTH 'Swings' and a
'Sandpit', I'd expect to see:
Mitchell Park
Bowden Park
Burgess Park
Rex Manor Playground
If this was limited to only looking for 2 or 3 features at a time,
even on a hefty DB of 300,000 records, this wouldn't be too difficult.
You would fetch all parks for each of the 2 or 3 features, then
eliminate all but the ones they share in common (exclusionary merging
of some sort). However, with 50 features, this becomes unusable, since
you'd have tons of redundancies when retrieving the data, and you'd
have to recursively iterate over 50 features.
Any way to combine the queries into one, and subquery it to eliminate
ones that don't have all the features requested in the query?
-Kevin
On Jul 7, 2009, at 5:44 PM, Kevin Elliott wrote:
> I have two models, associated with a HABTM (actually using
> has_many :through on both ends, along with a join table). I need to
> retrieve all ModelA's that is associated with BOTH of two ModelB's. I
> do NOT want all ModelA's for ModelB_1 concatenated with all ModelA's
> for ModelB_2. I literally want all ModelA's that are associated with
> BOTH ModelB_1 and ModelB_2. It is not limited to only 2 ModelB's, it
> may be up to 50 ModelB's, so this must scale.
>
> I can describe the problem using a variety of analogies, that I think
> better describes my problem than the previous paragraph:
>
> * Find all books that were written by all 3 authors together.
> * Find all movies that had the following 4 actors in them.
> * Find all blog posts that belonged to BOTH the Rails and Ruby
> categories for each post.
> * Find all users that had all 5 of the following tags: funny,
> thirsty, smart, thoughtful, and quick. (silly example!)
> * Find all people that have worked in both San Francisco AND San Jose
> AND New York AND Paris in their lifetimes.
>
> I've thought of a variety of ways to accomplish this, but they're
> grossly inefficient and very frowned upon.
>
> Taking an analogy above, say the last one, you could do something like
> query for all the people in each city, then find items in each array
> that exist across each array. That's a minimum of 5 queries, all the
> data of those queries transfered back to the app, then the app has to
> intensively compare all 5 arrays to each other (loops galore!). That's
> nasty, right?
>
> Another possible solution would be to chain the finds on top of each
> other, which would essentially do the same as above, but won't
> eliminate the multiple queries and processing. Also, how would you
> dynamicize the chain if you had user submitted checkboxes or values
> that could be as high as 50 options? Seems dirty. You'd need a loop.
> And again, that would intensify the search duration.
>
> Obviously, if possible, we'd like to have the database perform this
> for us, so, people have suggested to me that I simply put multiple
> conditions in. Unfortunately, you can only do an OR with HABTM
> typically.
>
> Another solution I've run across is to use a search engine, like
> sphinx or UltraSphinx. For my particular situation, I feel this is
> overkill, and I'd rather avoid it. I still feel there should be a
> solution that will let a user craft a query for an arbitrary number of
> ModelB's and find all ModelA's.
>
>
> How would *you* solve this problem?
>
>
> Thanks a bunch,
> Kevin
>
> (I've subsequently cross-posted this to StackOverflow's website since
> they have a target audience that encompasses more than Rails, and is
> still valid in those areas ---
> http://stackoverflow.com/questions/1095571/habtm-finds-with-and-joins-not-or
> )
--~--~---------~--~----~------------~-------~--~----~
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
