如何在Django ORM查询中对重复行关联的值求和

Alright, let's figure out how to fix that duplicate sum problem you're hitting with your Django models. The core issue here is that multiple ModelC records linking the same ModelA to a single ModelX (via the modelxx field) are causing the value from that ModelA to be counted multiple times in your sum.

In your example, for the Bar ModelX entry, you've got two ModelC records pointing back to modela1—so your initial queries would add that 10 value twice, plus the 10 from modela2, giving you 30 instead of the expected 20. Let's fix that.

Solution: Use Subquery with Distinct Values #

The key is to first get a unique set of ModelA values for each ModelX, then sum those unique values. Here's a clean way to do this with Django's ORM:

from django.db.models import Subquery, OuterRef, Sum, IntegerField

# First, fetch distinct ModelA values linked to each ModelX via modelxx
unique_modela_values = (
    ModelA.objects
    .filter(modelb__modelc__modelxx=OuterRef('id'))
    .distinct()  # This ensures each ModelA is only considered once per ModelX
    .values('value')
)

# Annotate each ModelX with the sum of these unique values
results = ModelX.objects.annotate(
    total=Subquery(
        # Aggregate the sum within the subquery for the current ModelX
        unique_modela_values.aggregate(total_sum=Sum('value'))['total_sum'],
        output_field=IntegerField()
    )
).filter(total__isnull=False).values('name', 'total')

Testing Against Your Sample Data #

When you run this with the example records you created:

• For Bar, the distinct ModelA values are 10 (from modela1) and 10 (from modela2), summing to 20.
• For Baz, only modela1 is linked (once), so the sum is 10.

The resulting queryset will look exactly like what you're expecting:

<QuerySet [{'name': 'Bar', 'total': 20}, {'name': 'Baz', 'total': 10}]>

Why This Works #

• The distinct() call in the subquery ensures that even if multiple ModelC records connect the same ModelA to a ModelX, we only include that ModelA's value once in the sum.
• Using Subquery lets us compute the sum specifically for each individual ModelX, avoiding the cross-join duplication that was breaking your initial attempts.

Alternative Approach: Filter by Unique ModelA IDs #

If you prefer another way to structure this, you can first get unique ModelA IDs per ModelX, then use that to filter the sum:

from django.db.models import Subquery, OuterRef, Sum, Q

# Get unique ModelA IDs linked to each ModelX
unique_modela_ids = (
    ModelA.objects
    .filter(modelb__modelc__modelxx=OuterRef('id'))
    .distinct()
    .values_list('id', flat=True)
)

# Sum only values from those unique ModelA records
results = ModelX.objects.annotate(
    total=Sum(
        'modelcs__modelb__modela__value',
        filter=Q(modelcs__modelb__modela__id__in=Subquery(unique_modela_ids))
    )
).filter(total__isnull=False).values('name', 'total')

This will give you the same correct results—just a slightly different way to approach the deduplication.

内容的提问来源于stack exchange，提问作者LiveFreeOrCode