如何让Django的ORM检查模型是否具有某种关系？

I've read that it's bad to avoid large IN clauses, because they are slow (especially with PostgreSQL).

我已经读过,避免使用大的IN子句是很糟糕的,因为它们很慢(特别是使用PostgreSQL)。

Say I have a class called Fridge, and a classes called Vegetables and Condiments.

假设我有一个名为Fridge的课程,还有一个叫做蔬菜和调味品的课程。

Both of these have ManyToMany relationships between themselves and Fridge.

这两者在他们自己和冰箱之间都有ManyToMany关系。

So something like:

所以类似于:

class Fridge(models.Model):
     condiments = models.ManyToManyField(Condiments)
     vegetables = models.ManyToManyField(Vegetables)

And here we have a QuerySet that represents our white fridges:

在这里,我们有一个QuerySet代表我们的白色冰箱:

qs = Fridges.objects.filter(color='white')

First question:

"Given a list of condiment IDs, get me all the fridges that have ANY of those condiments in them (modifying the original QuerySet).""

“鉴于一份调味品ID列表,请告诉我所有其中包含任何调味品的冰箱(修改原始QuerySet)。”

Second query:

"Given a list of vegetable IDs, get me all the fridges that have ALL of those vegetables in them (modifying the original QuerySet)."

“鉴于一份蔬菜ID列表,请告诉我所有这些蔬菜中含有所有蔬菜的冰箱(修改原始的QuerySet)。”

How on earth would I do that without building a list of fridge IDs and adding an IN clause to my queryset?

如果不构建冰箱ID列表并在我的查询集中添加IN子句,我怎么能这样做呢?

Here are solutions that do it with IN clauses (name changed versions of my existing solutions):

以下是使用IN子句(现有解决方案的名称更改版本)执行此操作的解决方案:

First query:

    condiment_ids = [...] # list of condiment IDs
    condiments = Condiment.objects.filter(
        id__in=condiment_ids).all()
    condiment_fridges = None
    for condiment in condiments:
        qs = condiment.fridge_set.all()
        if not condiment_fridges:
            condiment_fridges = qs
        else:
            condiment_fridges = condiment_fridges | qs
    qs = qs.filter(id__in=[l.id for l in condiment_fridges])

Second query:

    vegetable_ids = [...] # list of vegetable IDs
    vegetables = vegetable.objects.filter(id__in=vegetable_ids).all()
    vegetable_fridges = None
    for vegetable in vegetables:
        qs = vegetable.location_set.all()
        if not vegetable_fridges:
            vegetable_fridges = qs
        else:
            vegetable_fridges = vegetable_fridges & qs
    qs = qs.filter(id__in=[l.id for l in vegetable_fridges])

These solutions seem horrible and hackish and I was wondering if there was a better way to do them with Django's ORM.

这些解决方案看起来很糟糕,而且我很想知道是否有更好的方法来使用Django的ORM。

1 个解决方案

#1

Unless I'm misunderstanding the question then all you need is:

除非我误解了这个问题,否则你所需要的只是:

Fridge.objects.filter(condiments__in=[1,2,3,4,5])

There might be a more efficient way to find if a Fridge has all the condiments. Not tested but something like:

可能有一种更有效的方法来查找冰箱是否含有所有调味品。没有经过测试,但有类似:

max_conds = Condiment.objects.all().count()
result = Fridge.objects.annotate(conds=Count('condiments')).filter(conds=max_conds)

That could well be slower though depending on your db backend and the number of rows for each model.

根据您的数据库后端和每个模型的行数,这可能会慢一些。

#1