缩小多对多关系中可能的组合

Question

我有以下3个数据库表：

足球：

• id
• 名称

标签：

• id
• 名称

Foobar_标签：

• id
• foobar_id
• tag\u id

有许多Foobar，它们随机标记有一个或多个标记。

我收到标签列表-例如（'tag1'，'tag2'，'tag3'）

现在我想得到一个与foobar相关联的标签列表，其中foobar也与接收到的标签列表相关联。

为了更多地可视化这一点：

• foobar_1有标签“tag1”、“tag2”

• 结果：'tag2'

• 结果：'tag2'

• 结果：无

我使用的是Django，我目前的方法是这样的（foobar to tags是一个简单的m2m字段）：

if tag_list:
    available_tags = Tag.objects
    for tag in tag_list:
        available_tags = available_tags.filter(foobar__tags__tag=tag).exclude(tag=tag)
    available_tags = available_tags.distinct()          
else:
    available_tags = Tag.objects.all()

available_tags = available_tags.annotate(num_foobars=Count('foobar', distinct=True)) \
                                                  .order_by('-num_foobars') \
                                                  .exclude(num_foobars=0)

我得到了我想要的结果，但我不确定我在这里使用了正确的方法。当只过滤2个标签时，生成的SQL已经包含8个INNER JOINS，并且每个添加的标签都增长得非常快，因此速度非常慢。

这是查找时生成的SQL（'tag1'，'tag2'）

SELECT DISTINCT 
    "tag"."id", 
    "tag"."name", 
    COUNT(DISTINCT "foobar_tags"."foobar_id") AS "num_foobars" 
FROM "tag" 
INNER JOIN "foobar_tags" ON ( "tag"."id" = "foobar_tags"."tag_id" ) 
INNER JOIN "foobar" ON ( "foobar_tags"."foobar_id" = "foobar"."id" ) 
INNER JOIN "foobar_tags" T4 ON ( "foobar"."id" = T4."foobar_id" ) 
INNER JOIN "tag" T5 ON ( T4."tag_id" = T5."id" ) 
INNER JOIN "foobar_tags" T6 ON ( "tag"."id" = T6."tag_id" ) 
INNER JOIN "foobar" T7 ON ( T6."foobar_id" = T7."id" ) 
INNER JOIN "foobar_tags" T8 ON ( T7."id" = T8."foobar_id" ) 
INNER JOIN "tag" T9 ON ( T8."tag_id" = T9."id" ) 
WHERE (T5."name" = 'tag1' 
    AND NOT ("tag"."name" = 'tag1') 
    AND T9."name" = 'tag2' 
    AND NOT ("tag"."name" = 'tag2')) 
GROUP BY "tag"."id", "tag"."name" 
HAVING NOT (COUNT(DISTINCT "foobar_tags"."foobar_id") = 0) 
ORDER BY "num_foobars" DESC

• 查询是否可以优化（使用Django ORM或原始SQL）

Answer 1

每个附加标签都不需要连接。假设查询包含tag1和tag2，以下是它的sql：

select distinct tags.id, tags.name from tags inner join foobar_tags 
   on tags.id = foobar_tags.tagId 
   where fooId in 
      (select fooId from tags t inner join foobar_tags ft on t.id = ft.tagId 
          where 
             (select count(distinct name) from foobar_tags inner join tags 
               on tags.id = foobar_tags.tagId
               where fooId = ft.fooId and tags.name in('tag2','tag1')--tags query
             ) = 2 --number of tags in the query
    )
    AND
    name not in ('tag2','tag1')--tags query

我们通过计算属于我们的查询标记的相关标记，找到所有具有所有标记的foo。此计数应等于查询标记的数量。然后我们返回匹配的foo的标签，但不包括属于查询标签的标签。

您可以为任意数量的标记生成此查询，并且连接的数量将保持不变。

这是一把小提琴。