Django 1.8: Annotation of the queryset members count WITHOUT filtering

Question

How can I use the Django 1.8 ORM to count, how many members an object has?

For example: How can I SORT all workers by the count of the software, their company is using?

Bill 3 (Windows, Office, Visio)
Tom 2 (Windows, Office)
Anton 1 (Office)
Peter 0
Stephan 0

Code:

def Software(model.Model):
    name = models.CharField(max_length=200)

def Company(models.Model):
    software = models.ManyToManyField(Software)

def Worker(models.Model):
    company = models.ForeignKey(Company)

software = Software.objects.filter(price>60)    # [<Software: Windows>,...] 
workers = Worker.objects.filter(company__software__in=software).
             annotate(software_count=Count('company__software‘))

for worker in workers:
    print „%s %s (%s)" % (worker.name, worker.software_count, ...)

The tricky part is that I don’t want to filter BUT ONLY want to annotate the count of the software, used of the given list.

Any help is appreciated :-)

Thx for the input. Yes - that gives the number of *all* Software, set. I need something like `Worker.objects.annotate(software_count=Count(company_software_in=software))` — buLLfisH, Aug 21 '15 at 18:08

ascripter · Accepted Answer · 2015-08-22T12:38:16.633

1

Looks like conditional aggregation (or annotation in this case). Code is untested, but should be something like the following:

class Software(model.Model):
    name = models.CharField(max_length=200)
    price = models.DecimalField(max_digits=8, decimal_places=2)

class Company(models.Model):
    software = models.ManyToManyField(Software)

class Worker(models.Model):
    company = models.ForeignKey(Company)

workers = Worker.objects.all().annotate(software_count=Sum(
    Case(When(company__software__price__gt=60, then=1), output_field=IntegerField())
))

for worker in workers:
    print "%s %s" % (worker.name, worker.software_count)

However, you don't have the list of the software used. I think to get this in the same query you have to dive deeper into SQL, I see no way to achieve it with the ORM. Well, maybe it's possible with a Func() expression and using the SQL-function GROUP_CONCAT. But I fear this is beyond my level.

edited Aug 22 '15 at 12:38

answered Aug 21 '15 at 21:23

ascripter

5,665
12
45
68

1

Thank you so much! Your comment helped me a out finding the solution. Your annotation counted **all** Software (not the one in the list). But with the following annotation I get the count of the software in that list. `Worker.objects.annotate(software_count=Sum( Case(When(company__software__in=software, then=1), default=0, output_field=IntegerField()))).order_by("-software_count")` – buLLfisH Aug 22 '15 at 10:59
There was a mistake in my code. Try replacing the When-clause by When(company__software__price__gt=60, then=1). I changed it in my original post, but of course you want to check price > 60 and not software > 60. Then everything is inside a single query – ascripter Aug 22 '15 at 12:39

Django 1.8: Annotation of the queryset members count WITHOUT filtering

1 Answers1