python - Django filter, paginate and annotate paginated results -

i have object reports , reportsubscriber , want count number of subscribers of report.

one solution annotating. have lots of reports annotating of them takes ~6 seconds, thought maybe it's better annotate after paginating:

filter_search = reportfilter(request.get, queryset=report.objects.filter(         created_at__gt=start_date,         created_at__lte=end_date,         is_confirmed__exact=true,     ).annotate(sub_count=count("reportsubscriber")).order_by('-sub_count'))  paginator = paginator(filter_search, 20)  result = paginator.page(1).object_list.annotate(                 sub_count=count("reportsubscriber")) 

it worked, took same time , when checked queries, still went through rows in report_subscriber table. tried using .extra()

filter_search = reportfilter(request.get, queryset=report.objects.filter(             created_at__gt=start_date,             created_at__lte=end_date,             is_confirmed__exact=true,         ))  paginator = paginator(filter_search, 20) paged_reports = paginator.page(1)  result = filter_search.qs.extra(             select={                 'sub_count': 'select count(*) reports left outer join report_subscribers  \                              on (reports.id = report_subscribers.id) \                              reports.id = report_subscribers.id \                              , report_subscribers.report_id in %s \                             ' % "(%s)" % ",".join([str(r.id) r in paged_reports.object_list])             },             order_by=['sub_count']         ) 

but still didn't worked. got 1 static number of subscribers reports. missing, , maybe there better ways accomplish this? thanks

i can't give definitive answer, believe problem when paginated, entire query must executed paginator knows how many pages there are. should think you'll better off getting rid of annotation before pagination:

filter_search = reportfilter(request.get, queryset=report.objects.filter(         created_at__gt=start_date,         created_at__lte=end_date,         is_confirmed__exact=true,     ).order_by('-sub_count'))  paginator = paginator(filter_search, 20)  result = paginator.page(1).object_list.annotate(                 sub_count=count("reportsubscriber")) 

i trust example object_list queryset can annotate, if it's list of objects, can annotate each page of results like:

pageids = [report.id report in paginator.page(1).object_list] result = report.objects.filter(id__in=pageids).annotate(                 sub_count=count("reportsubscriber")) 

but shooting in dark. nothing you're doing looks crazy, unless dataset huge, can imagine problem poorly indexed query. want profile actual query that's being generated. can sql executing project django shell given start_date , end_data:

report.objects.filter(         created_at__gt=start_date,         created_at__lte=end_date,         is_confirmed__exact=true,     ).order_by('-sub_count').query 

and run same query psql command line on database using explain. you'll have bit of reading figure out how interpret results.