How to use annotate to Count items from a Django QuerySet

Question

I am modeling Applications and the ways they are installed on user's devices as follows:

class WindowsUserApplications(models.Model):
    application_name = models.CharField(max_length=60, null=False, unique=True)
    category = models.ForeignKey('ApplicationCategory', null=False, default=10, on_delete=models.SET_DEFAULT)

class WindowsUserInstalls(models.Model):
    device_name = models.CharField(max_length=15, null=False)
    version = models.CharField(max_length=30, null=False)
    application = models.ForeignKey('WindowsUserApplications', related_name='windows_user_applications', null=False, on_delete=models.CASCADE)

And then, on the view I want to get a list of the installed applications like this:

def user_inventory(request, *args, **kwargs):
    user_applications = WindowsUserInstalls.objects.annotate(Count('application'))
    template = 'webapp/user_inventory.html'
    context = {
        'user_applications' : user_applications,
    }
    return render(request, template, context)

However, I keep getting repeated lines for the installed apps and all of them with an install count of 1, instead of getting them summarized.

I have tried other approaches such as adding .values('application') to the query, with no luck.

Thanks in advance for any hints.

try `WindowsUserInstalls.objects.order_by('application').annotate(Count('application'))` — Elgin Cahangirov, Apr 20 '21 at 22:02
Thanks @ElginCahangirov, the QuerySet I got was exactly the same, with repeated lines and a value of 1 for each. — Bernardo, Apr 21 '21 at 08:11

score 0 · Accepted Answer · answered Apr 23 '21 at 13:53

I figured out a way (which I'm not sure if is optimal) to get all the fields I need on the view.

def user_inventory(request, *args, **kwargs):
    user_applications = WindowsUserInstalls.objects.select_related(
            'application', 'category'
        ).values(
            'application', 'application__application_name', 'application__category__name'
        ).annotate(
            Count('application')
        )
    template = 'webapp/user_inventory.html'
    context = {
        'user_applications': user_applications,
    }
    return render(request, template, context)

The trick was adding the .select_related('application', 'category') call to get values from other tables which are related via a Foreign Key and also the .values() call to fetch the values that I need, including those that are coming from the related tables: application__application_name and application__category_name. Notice the double underscore.

Thanks to that, the annotate(Count('application')) actually works and I can get the values in the template like this:

{% for application in user_applications %}
    <tr>
        <td>{{ application.application }}</td>
        <td>{{ application.application__application_name }}</td>
        <td>{{ application.application__count }}</a></td>
        <td>{{ application.application__category__name }}</a></td>
    </tr>
{% endfor %}

If there's a better way to do it, please by all means let me know.

How to use annotate to Count items from a Django QuerySet

1 Answers1