Count on multiple fields in Django querysets

Question

I have a model representing a transaction between two users, like this:

class Transaction(models.Model):
    buyer = models.ForeignKey(
        Person, on_delete=models.SET_NULL, null=True, related_name="bought"
    )
    seller = models.ForeignKey(
        Person, on_delete=models.SET_NULL, null=True, related_name="sold"
    )
    product = models.ForeignKey(Product, on_delete=models.SET_NULL, null=True)

I would like to get the number of transactions for each user (either as a buyer or a seller). If a I want to count on only one field, I can just do :

Transaction.objects.values('seller').annotate(Count('seller'))

but I can't manage to do it on two fields at the same time in 1 query. Is there a way to do that ?

Thanks

Answer 1

I just came across this question from myself, so I'll post an answer, in case someone ever need it: The obvious idea is to use two Count in a single annotate, but as the django doc says, using multiple aggregations in annotate will yield the wrong result. It does work with Count, using distinct keyword, for Django 2.2 or 3:

from django.db.models import Count
result = Person.objects.annotate(
    transaction_count=Count("bought", distinct=True) + Count("sold", distinct=True)
).values("id", "transaction_count")

For Django < 2.2, you can use subqueries:

from django.db.models import Count, OuterRef, F

buyer_subquery = (
    Transaction.objects.filter(buyer_id=OuterRef("id"))
    .values("buyer_id")
    .annotate(subcount=Count("id"))
    .values("subcount")
)
seller_subquery = (
    Transaction.objects.filter(seller_id=OuterRef("id"))
    .values("seller_id")
    .annotate(subcount=Count("id"))
    .values("subcount")
)
Person.objects.annotate(
    buyer_count=buyer_subquery,
    seller_count=seller_subquery,
    transaction_count=F("buyer_count") + F("seller_count"),
).values("id", "transaction_count")

Answer 2

Maybe something like this would work?

Transaction.objects.annotate(
    num_sellers=Count('seller'), num_buyers=Count('buyer')
)