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 |
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.