4

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.

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.

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:

 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:

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

EDIT: The solution to my problem was Subquery()

Supergator
  • 41
  • 5

2 Answers2

5

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()
neverwalkaloner
  • 46,181
  • 7
  • 92
  • 100
  • interesting, however instead of expected output, I am getting value: 1 for each item. For sure, there are contracts which are duplicated – Supergator Jul 03 '18 at 09:42
  • @Supergator I suppose this is because of default ordering. Try to add `order_by()` as in my update. See detail here: https://docs.djangoproject.com/en/2.0/topics/db/aggregation/#interaction-with-default-ordering-or-order-by – neverwalkaloner Jul 03 '18 at 09:46
  • Thanks, that's true, I had some ordering before in my query. Now, I am getting good counting values, the problem is that, it has reduced amount of items in the output - I have to keep original amount if items. – Supergator Jul 03 '18 at 10:15
  • you have any idea, how to get counting values without reducing amount of items? – Supergator Jul 04 '18 at 08:04
  • 1
    @Supergator try this solution: https://stackoverflow.com/a/42648980/641249 – neverwalkaloner Jul 04 '18 at 08:09
0

Solutions:

counttraker=Traker.objects.values('contract_number').annotate(Count('contract_number'))
subquery=counttraker.filter(contract_number=OuterRef('contract_number').values('contract_number__count')[:1]
traker=Traker.objects.annotate(count=Subquery(subquery))
mike
  • 1