HABTM finds with AND, rather than OR,

Question

I have two HABTM related models (actually using has_many: through both ends along with the join table). I need to get all ModelA that are associated with BOTH of two ModelB. I do NOT want all ModelAs for ModelB_1 to be combined with all ModelAs for ModelB_2. I literally want all ModelAs to be associated with BOTH ModelB_1 and ModelB_2. It is not limited to only 2 ModelBs, it can be up to 50 ModelB, so it should scale.

I can describe the problem using many analogies, which I think describes my problem better 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 thought of various ways to achieve this, but they are very inefficient and very disapproved.

If we take the analogy above, say the last one, you can do something like a query for all people in each city, and then find the elements in each array that exist in each array. With a minimum of 5 requests, all data from these requests is transferred back to the application, then the application must intensively compare all 5 arrays with each other (loops abound!). This is nasty, right?

Another possible solution would be to combine finds above each other, which would essentially be the same as above, but would not eliminate multiple queries and processing. Also, how would you dynamize a chain if you have custom flags or values ​​that can have up to 50 options? It seems dirty. You will need a loop. Again, this will increase the duration of the search.

, , , , , , . , OR HABTM.

, , , sphinx UltraSphinx. , , . , , ModelB ModelAs.

?

Answer 1

:

• ModelA, ModelB ( ), ModelBs, , , OR (.. where ModelB = 'ModelB_1' or ModelB = 'ModelB_2'). "ModelA", ModelB.

• ModelA, ( ). () ModelB *.

• 'having', , count(*) ModelB,

:

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. , , MySQL, SQL - model_as .