I've two models, connected having a HABTM (really using has_many :through on finishes, together with a join table). I have to retrieve all ModelAs that's connected with Each of two ModelBs. I don't want all ModelAs for ModelB_1 concatenated with all of ModelAs for ModelB_2. I literally want all ModelAs which are connected with BOTH ModelB_1 and ModelB_2. It's not restricted to only 2 ModelBs, it might be as much as 50 ModelBs, which means this must scale.

I'm able to describe the issue using a number of analogies, which i think better describes my problem compared to 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 have considered several different ways to do this, but they are grossly inefficient and incredibly frowned upon.

Taking an example above, the 4g iphone, you could do this something similar to query for the folks each city, then look for products in every array which exist across each array. That's no less than 5 queries, all of the data of individuals queries transfered to the application, then your application needs to intensively compare all 5 arrays to one another (loops in abundance!). That's nasty, right?

Another possible solution is always to chain the finds on the top of one another, which may basically do just like above, but will not get rid of the multiple queries and processing. Also, how does one dynamicize the chain should you have had user posted checkboxes or values that may be up to 50 options? Appears dirty. You'd require a loop. And again, that will intensify the search duration.

Clearly, if at all possible, we'd enjoy having the database perform this for all of us, so, individuals have recommended in my experience which i to put it simply multiple conditions in. Regrettably, you are able to only do an OR with HABTM typically.

Another solution I have stumble upon is by using a internet search engine, like sphinx or UltraSphinx. For my particular situation, Personally i think this really is overkill, and I'd rather cure it. I still feel there must be an answer which will let a person craft a question to have an arbitrary quantity of ModelBs and discover all ModelAs.

How does one solve this issue?

You might do that:

  1. develop a query out of your ModelA, joining ModelB (with the join model), blocking the ModelBs which have among the values that you're searching for, that's putting them in OR (i.e. where ModelB = 'ModelB_1' or ModelB = 'ModelB_2'). With this particular query the end result set may have multiple 'ModelA' rows, exactly one row for every ModelB condition satisfied.

  2. give a group by condition towards the query around the ModelA posts you'll need (even these if you want). The count() for every row is equivalent to the amount of ModelB conditions satisfied*.

  3. give a 'having' condition choosing just the rows whose count(*) is equivalent to the amount of ModelB conditions you must have satisfied

example:

model_bs_to_find = [100, 200]
ModelA.all( :joins=>{:model_a_to_b=>:model_bs}, 
            :group=>"model_as.id", 
            :select=>"model_as.*",
            :conditions=>["model_bs.id in (?)", model_bs_to_find], 
            :having=>"count(*)=#{model_bs_to_find.size}")

N.B. the audience and choose parameters specified for the reason that way works in MySQL, the conventional SQL method of doing so is always to place the whole listing of model_as posts both in the audience and choose parameters.