Django aggregate query with filters

user_8507789 0
Viewed 37

i have two tables.Template and Tracker tables. with one to many relationship. i need to show all the uploaded templates for a particular user. also i need to order by with the download count.

i tried the following query. but it's not getting the all downloaded count.

Template.objects.filter(tracker__type='upload', user_id=300).annotate(
    download_count=Count(
       'tracker',
       filter=Q(tracker__type='download')
    )
).order_by("-download_count")

Template Table

id title description
1 AAA AAA
2 AAA AAA
3 AAA AAA

Tracker Table

id type user_id template_id
1 download 100 1
2 download 200 1
3 upload 300 1
django-models django
1 Answers

You can filter with an Exists subquery [Django-doc] and then annotate the Templates:

from django.db.models import Count, Exists, OuterRef

Template.objects.filter(
    Exists(Tracker.objects.filter(
        template_id=OuterRef('pk'), user_id=300, tracker_type='upload'
    ))
).annotate(
    total_downloads=Count('tracker', filter=Q(tracker__type='download'))
).order_by('-total_downloads')

This will return a QuerySet of Templates that have been uploaded by a user with as primary key 300 and these Template objects will contain an extra attribute .total_downloads that will list the number of downloads for that Template.

user_675790
Related