如何在连接它们之前限制具有n到n关系的表中的记录，以避免在django ORM中重复记录? [英] How to limit records in tables with n to n relation before joining them , to avoid duplicates in django ORM?

问题描述

扩展我以前的

Extending my previous question on stack-overflow. I have four tables:

A <--- Relation ---> B ---> Category

(所以A和B之间的关系是n到n，其中B和Category之间的关系是n到1) 关系将A的强度"存储在B中.我需要计算每个类别中A的强度，然后找到最大值"结果.可以使用以下方法实现:

(So the relation between A and B is n to n, where the relation between B and Category is n to 1) Relation stores 'Intensity' of A in B. I need to calculate the intensity of A in each Category and find the Maximum result. It is achievable using:

A.objects.values(
    'id', 'Relation_set__B__Category_id'
).annotate(
     AcIntensity=Sum(F('Relation_set__Intensity'))
).aggregate(
     Max(F('AcIntensity'))
)['AcIntensity__max']

现在我需要基于B中的某些字段来过滤强度:

Now I need to filter the intensities based on some fields in B beforhand:

A.objects.values(
    'id', 'Relation_set__B__Category_id'
).filter(
    Relation_set__B__BType=type_filter
).annotate(
    AcIntensity=Sum(F('Relation_set__Intensity'))
).aggregate(
    Max(F('AcIntensity'))
)['AcIntensity__max']

但是我需要避免由于表连接而造成的重复，从而使计算混乱.(除了用于定义过滤的那些字段，我不需要B中的任何字段)
有没有办法使用Django ORM来实现这一目标?

However I need to avoid duplication resulted due to table join which messes the calculation up.(beside those field to define filtration, I do not need any fields in B)
Is there a way to achieve this using Django ORM?

更新
我认为我需要的是在查询数据库之前限制关联表(基于B筛选器)中的记录.我该怎么办?
(也许使用Prefetch_related和Prefetch吗?)

Update
I think what I need is to limit the records in Relation table (based on B filters) before querying the database. How can I do that?
(Maybe using Prefetch_related and Prefetch?)

推荐答案

最后，我已经使用堆栈溢出中找到更多详细信息发布. 所以最终的代码将是:

Finally I've done it using conditional aggregation.
You could find more details in this stack-overflow post. So the final code would be:

result = A.objects.values(
    'id', 'Relation_set__B__Category_id'
).annotate(
    AcIntensity=Sum(
        Case(
            When(
               q_statement_for_filter,then=F('Relation_set__Intensity'),
            ),
            default=0,
            output_field=FloatField()
        )
    )
).exclude(
    AcIntensity=0
).aggregate(
    Max(F('AcIntensity'))
)['AcIntensity__max']

请注意，"q_statement_for_filter"不能为空Q().

Notice that 'q_statement_for_filter' cannot be an empty Q().

这篇关于如何在连接它们之前限制具有n到n关系的表中的记录，以避免在django ORM中重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！