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

Lotram · Accepted Answer · 2020-07-08T12:23:25.730

12

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

edited Jul 08 '20 at 12:23

answered May 29 '20 at 14:51

Lotram

729
6
17

Thanks for this - just curious though, why is the `distinct=True` necessary for each count? I used without that just to see and was getting strange totals for my models that I couldn't explain. When I used with it works perfectly to add counts of multiple models together. – blueblob26 Feb 18 '22 at 18:00
thanks for your help, however, I don't know why I should use the "distinct" method as a KWARG with annotate method. when I used it, it printed out the required results but don't understand how it works with annotate method so, could you explain that, please? – Abdelhamed Abdin Oct 16 '22 at 04:58
1

Have you read the [django doc](https://docs.djangoproject.com/en/4.1/topics/db/aggregation/#combining-multiple-aggregations) I'm referencing in my answer ? `distinct` uses a `DISTINCT` in SQL, which prevents from counting the same object more than once. This could happen without `DISTINCT` because you're joining mulitple tables. This [django issue](https://code.djangoproject.com/ticket/10060) explains why this is a problem – Lotram Oct 17 '22 at 09:28

score 0 · Answer 2 · answered Nov 06 '17 at 11:47

0

Maybe something like this would work?

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

answered Nov 06 '17 at 11:47

noobinatrixxx

33
5

This will return two counters, whereas I'd like a sum of the two. If user 'A' has been a seller for 3 transactions, and a buyer for 5, I would like to have {'user': 'A', 'count': 8} – Lotram Nov 06 '17 at 11:59
Take a look here: https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#aggregating-annotations – noobinatrixxx Nov 06 '17 at 13:04

score -1 · Answer 3 · answered Nov 06 '17 at 12:17

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/

Count on multiple fields in Django querysets

3 Answers3

Linked