Rails:通过多对多关联查找实体

I have a model A, and a model B. A has_and_belongs_to_many Bs, and vice versa.

我有一个模型a和一个模型b。a has_and_belongs_to_many Bs，反之亦然。

Now, I want to find an object/entity within A that has_and_belongs_to certain objects within B (say B1 and B2). How can I do that efficiently within Rails? My current solution is something like this:

现在，我想在A中找到一个对象/实体has_and_belongs_to B中的某些对象(比如B1和B2)。我如何在Rails中有效地做到这一点?我目前的解决方案是这样的:

A.all.select {|a| a.bs.sort == [B1, B2]}.first

It basically iterates through all objects within A and checks if it has_and_belongs_to the correct Bs. That is very inefficient. Is there a better way to do this?

它基本上遍历所有的对象，并检查它是否has_and_belongs_to正确的Bs。这是非常低效的。有更好的方法吗?

2 个解决方案

#1

You can do this with nested sub-queries, which is a working solution but not necessarily an efficient one, so you'll have to run some benchmarks.

可以使用嵌套子查询来实现这一点，嵌套子查询是一种有效的解决方案，但不一定是一种有效的解决方案，因此必须运行一些基准测试。

The following involves three nested queries performed on the join_table between A and B. You first determine the id's of all B's (call these excluded_bs) that are not either B1 or B2. Then, you determine which A's are in a relationship with these excluded_bs and call them excluded_as. All the A's that are not in excluded_as are exactly the ones we want (call them included_as). Once you have included_as just query the A table.

下面涉及在A和B之间的join_table上执行的三个嵌套查询，您首先确定不是B1或B2的所有B的id(调用这些exclusive ded_bs)。然后，您将确定哪些A与这些排斥性的关系处于关系中，并将其称为独占式。所有不在exclusive ded_as中的A都是我们想要的(称为included_as)。一旦包含了included_as，只需查询A表。

excluded_bs = %(SELECT B_id FROM join_table WHERE B_id NOT IN (:included_bs))
excluded_as = %(SELECT A_id FROM join_table WHERE B_id IN (#{excluded_bs}))
included_as = %(SELECT A_id FROM join_table WHERE A_id NOT IN (#{excluded_as}))

A.where("id IN (included_as)", :included_bs => [B1.id, B2.id])

This should give you all the A's that are in a relationship with exactly B1 and B2, but not with any others. You might be able to clean this up a bit and make it more efficient, but it should at least work.

这应该会给你所有与B1和B2相关的A，但不与其他的。你也许可以稍微清理一下，让它更有效率，但它至少应该起作用。

EDIT:

编辑:

Whoops! To trim off those that only have either B1 or B2, try a GROUP BY. Change the last sub-query to

哎呀!要去掉那些只有B1或B2的元素，可以尝试一组BY。将最后一个子查询更改为。

included_as = %(SELECT A_id, COUNT(*) as Total FROM join_table WHERE A_id NOT IN (#{excluded_as}) GROUP BY A_id HAVING Total = :count)

and the main query to

以及主查询。

Bs = [B1, B2]
A.where("id IN (SELECT A_id FROM (#{included_as}))", :included_bs => Bs.map(&:id), :count => Bs.count)

#2

You can filter on habtm associations:

你可以过滤habtm的关联:

A.joins(:bs).where("bs.id" => [B1, B2]).first

A.joins(:bs).where("bs.id" => [B1, B2]).all

To ensure that only the items with exactly two associations are returned, use

要确保只返回两个关联的项，请使用

A.joins(:bs).where("bs.id" => [B1, B2]).group("as.id HAVING COUNT(bs.id) = 2")

#1