Count on multiple fields in Django querysets

user_5659966 0
Viewed 4832

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

django
3 Answers

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")
user_56599660

Maybe something like this would work?

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

Filters can reference fields on the model¶

In the examples given so far, we have constructed filters that compare the value of a model field with a constant. But what if you want to compare the value of a model field with another field on the same model?

Django provides F expressions to allow such comparisons. Instances of F() act as a reference to a model field within a query. These references can then be used in query filters to compare the values of two different fields on the same model instance.....

Just reference the django documentation https://docs.djangoproject.com/en/1.11/topics/db/queries/

user_88619380
Related