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 |
CodePudding user response:
You can filter with an Exists
subquery [Django-doc] and then annotate the Template
s:
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 Template
s 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
.