HABTM用“AND”连接，NOT“OR” [英] HABTM finds with "AND" joins, NOT "OR"

问题描述

我有两个模型，与一个HABTM（实际上使用has_many：两端，连同一个连接表）。我需要检索与两个ModelB的BOTH相关联的所有ModelAs。我不想让ModelB_1的所有ModelAs与ModelB_2的所有ModelAs连接。我真的想要所有ModelA与BOTH ModelB_1和ModelB_2相关联。它不仅限于2个ModelB，它可以达到50个ModelB，因此必须缩放。

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 ModelAs that is associated with BOTH of two ModelBs. I do NOT want all ModelAs for ModelB_1 concatenated with all ModelAs for ModelB_2. I literally want all ModelAs that are associated with BOTH ModelB_1 and ModelB_2. It is not limited to only 2 ModelBs, it may be up to 50 ModelBs, 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.

以上类比，说最后一个，你可以对每个城市的所有人进行查询，然后在每个数组中查找存在的项目跨越每个阵列。这是至少5个查询，这些查询的所有数据转移回应用程序，然后应用程序必须深入比较所有5个阵列彼此（循环！）。这是讨厌的，对吧？

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?

另一种可能的解决方案是将搜索链接在彼此之上，这本质上会做同样的，但不会消除多个查询和处理。此外，如果您有用户提交的复选框或值可能高达50个选项的值，您如何动态化链？看起来很脏。你需要一个循环。再次，这将加强搜索持续时间。

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.

显然，如果可能，我们希望数据库为我们执行这个，所以，人们建议我我只是把多个条件。不幸的是，你只能做HABTM一个OR。

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.

我遇到的另一个解决方案是使用搜索引擎，如狮身人面像或UltraSphinx。对于我的特殊情况，我觉得这是多余的，我宁愿避免。我仍然觉得应该有一个解决方案，让用户为任意数量的ModelB制作一个查询，并找到所有的ModelAs。

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 ModelBs and find all ModelAs.

如何解决这个问题？ / p>

How would you solve this problem?

推荐答案

您可以这样做：

1. 从ModelA构建一个查询，加入ModelB（通过连接模型），过滤具有您要查找的值之一的ModelB，即将它们置于OR（即 where ModelB ='ModelB_1'或ModelB ='ModelB_2'）。使用此查询，结果集将具有多个ModelA行，每个ModelB条件满足一行。

1. build a query from your ModelA, joining ModelB (through the join model), filtering the ModelBs that have one of the values that you are looking for, that is putting them in OR (i.e. where ModelB = 'ModelB_1' or ModelB = 'ModelB_2'). With this query the result set will have multiple 'ModelA' rows, exactly one row for each ModelB condition satisfied.

添加分组条件到您需要的ModelA列上的查询（如果您愿意的话，甚至全部）。 每行的计数（）等于满足ModelB条件的数量*。

add a group by condition to the query on the ModelA columns you need (even all of them if you wish). The count() for each row is equal to the number of ModelB conditions satisfied*.

添加having只有 count（*）的行等于您需要满足的ModelB条件数

add a 'having' condition selecting only the rows whose count(*) is equal to the number of ModelB conditions you need to have satisfied

示例：

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}")

注意以这种方式指定的组和选择参数将在MySQL中工作，标准的SQL方法是将group_as列的整个列表放在group和select参数中。

N.B. the group and select parameters specified in that way will work in MySQL, the standard SQL way to do so would be to put the whole list of model_as columns in both the group and select parameters.

这篇关于HABTM用“AND”连接，NOT“OR”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！