Q: Django admin annotation is multiplied if search_field (by related FK), i have problem with multiplation of annotation...
if i use search_field by email useremail__email (related fk of user model) ... then my annotation is multipled
the problem is if the table with profileemails is joined (without search it works just good)
in example:
i have user named admin and emails.. admin1, admin2, admin3
then search is done by admin
the result is sum * 3 (because 3 emails)
my model:
class Payment(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    amount = models.IntegerField()


class SubPayment(models.Model):
    payment = models.ForeignKey(Payment, on_delete=models.CASCADE)
    amount = models.IntegerField()


class UserEmail(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    email = models.CharField(max_length=128)

in admin i have User model where i want to display sum and count of subpayments:
class UserAdmin(admin.ModelAdmin):
    list_display = ("username", "payment_count", "payment_sum")
    search_fields = (
        'useremail__email',
        'username'
    )

def payment_count(self, obj):
    return obj.payment_count

def payment_sum(self, obj):
    return obj.payment_sum

def get_queryset(self, request, *args, **kwargs):

    queryset = super().get_queryset(request, *args, **kwargs).annotate(
            payment_amount=Sum('payment__amount'),
            payment_count=Count('payment__subpayment'),
            payment_sum=Sum('payment__subpayment__amount'),
        )
    return queryset

I tried use distinct=True , but it remove duplicite subpayments with same amount...
tried on django 2.2 and 3.17
works good:

works wrong:

Any help?
Thanks!

A: The most effective way is probably to work with a Subquery expressions [Django-doc]:
from django.db.models import OuterRef, Subquery, Sum

def get_queryset(self, request, *args, **kwargs):

    queryset = super().get_queryset(request, *args, **kwargs).annotate(
        payment_amount=Subquery(
            Payment.objects.filter(user=OuterRef('pk')).values('user').annotate(
                total=Sum('amount')
            ).values('total').order_by('user')[:1]
        ),
        payment_count=Count('payment__subpayment', distinct=True),
        payment_sum=Subquery(
            SubPayment.objects.filter(payment__user=OuterRef('pk')).values('payment__user').annotate(
                total=Sum('amount')
            ).values('total').order_by('payment__user')[:1]
        )
    )
    return queryset
