Django QuerySet: additional field for counting value's occurence

I have a QuerySet object with 100 items, for each of them I need to know how many times a particular contract_number occurs in the contract_number field.

QuerySet

contract_number

contract_number

Example of expected output:

[{'contract_number': 123, 'contract_count': 2}, {'contract_number': 456, 'contract_count': 1} ...]


This means that value 123 occurs 2 times for the whole contract_number field.

contract_number

Important thing: I cannot reduce the amount of items, so grouping won't work here.

The SQL equivalent for this would be an additional field contract_count as below:

contract_count

SELECT *,
(SELECT count(contract_number) FROM table where t.contract_number = contract_number) as contract_count
FROM table as t


The question is how to do it with a Python object. After some research, I have found out that for more complex queries the Queryset extra method should be used. Below is one of my tries, but the result is not what I have expected

queryset = Tracker.objects.extra(
select={
'contract_count': '''
SELECT COUNT(*)
FROM table
WHERE contract_number = %s
'''
},select_params=(F('contract_number'),),)


My models.py:

models.py

class Tracker(models.Model):
contract_number = models.IntegerField()


EDIT:
The solution to my problem was Subquery()

1 Answer
1

You can use annotation like this:

from django.db.models import Count
Tracker.objects.values('contract_number').annotate(contract_count=Count('contract_number')).order_by()


order_by()

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.