How to apply a for statement to simplify repeated code in Django queryset-CodePudding

The filter is applied according to team_parameter(request.GET.get('team)) and it has very repetitive code. At the end of the if statement, no filter is applied only if team_parameter is 'ALL'. I think a for statement is necessary to minimize this code, but I did not know how to apply it, so I asked a question. Please let me know which loops are needed to simplify the code below. Help.

[views.py]

team_parameter = request.GET.get('team')

if team_parameter == 'A' and not team_parameter:
    monthly_enroll = Feedback.objects.filter(uploader_id__contact__team='A')\
.values('uploader_id__first_name').distinct().order_by('uploader_id__first_name')\
.annotate(jan=Count('client_id', filter=Q(enroll_date__gte='2022-01-01', enroll_date__lte='2022-01-31')),
          feb=Count('client_id', filter=Q(enroll_date__gte='2022-02-01', enroll_date__lte='2022-02-28')),
          mar=Count('client_id', filter=Q(enroll_date__gte='2022-03-01', enroll_date__lte='2022-03-31')),
          apr=Count('client_id', filter=Q(enroll_date__gte='2022-04-01', enroll_date__lte='2022-04-30')),
          may=Count('client_id', filter=Q(enroll_date__gte='2022-05-01', enroll_date__lte='2022-05-31')),
          jun=Count('client_id', filter=Q(enroll_date__gte='2022-06-01', enroll_date__lte='2022-06-30')),
          jul=Count('client_id', filter=Q(enroll_date__gte='2022-07-01', enroll_date__lte='2022-07-31')),
          aug=Count('client_id', filter=Q(enroll_date__gte='2022-08-01', enroll_date__lte='2022-08-31')),
          sept=Count('client_id', filter=Q(enroll_date__gte='2022-09-01', enroll_date__lte='2022-09-30')),
          oct=Count('client_id', filter=Q(enroll_date__gte='2022-10-01', enroll_date__lte='2022-10-31')),
          nov=Count('client_id', filter=Q(enroll_date__gte='2022-11-01', enroll_date__lte='2022-11-30')),
          dec=Count('client_id', filter=Q(enroll_date__gte='2022-12-01', enroll_date__lte='2022-12-31')),)\
.values_list('uploader_id__first_name', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sept', 'oct','nov', 'dec')\
.order_by('uploader_id__first_name')
elif team_parameter == 'B':
    monthly_enroll = Feedback.objects.filter(uploader_id__contact__team='B')\
.values('uploader_id__first_name').distinct().order_by('uploader_id__first_name')\
.annotate(jan=Count('client_id', filter=Q(enroll_date__gte='2022-01-01', enroll_date__lte='2022-01-31')),
          feb=Count('client_id', filter=Q(enroll_date__gte='2022-02-01', enroll_date__lte='2022-02-28')),
          mar=Count('client_id', filter=Q(enroll_date__gte='2022-03-01', enroll_date__lte='2022-03-31')),
          apr=Count('client_id', filter=Q(enroll_date__gte='2022-04-01', enroll_date__lte='2022-04-30')),
          may=Count('client_id', filter=Q(enroll_date__gte='2022-05-01', enroll_date__lte='2022-05-31')),
          jun=Count('client_id', filter=Q(enroll_date__gte='2022-06-01', enroll_date__lte='2022-06-30')),
          jul=Count('client_id', filter=Q(enroll_date__gte='2022-07-01', enroll_date__lte='2022-07-31')),
          aug=Count('client_id', filter=Q(enroll_date__gte='2022-08-01', enroll_date__lte='2022-08-31')),
          sept=Count('client_id', filter=Q(enroll_date__gte='2022-09-01', enroll_date__lte='2022-09-30')),
          oct=Count('client_id', filter=Q(enroll_date__gte='2022-10-01', enroll_date__lte='2022-10-31')),
          nov=Count('client_id', filter=Q(enroll_date__gte='2022-11-01', enroll_date__lte='2022-11-30')),
          dec=Count('client_id', filter=Q(enroll_date__gte='2022-12-01', enroll_date__lte='2022-12-31')),)\
.values_list('uploader_id__first_name', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sept', 'oct','nov', 'dec')\
.order_by('uploader_id__first_name')
elif team_parameter == 'C':
    monthly_enroll = Feedback.objects.filter(uploader_id__contact__team='C')\
.values('uploader_id__first_name').distinct().order_by('uploader_id__first_name')\
.annotate(jan=Count('client_id', filter=Q(enroll_date__gte='2022-01-01', enroll_date__lte='2022-01-31')),
          feb=Count('client_id', filter=Q(enroll_date__gte='2022-02-01', enroll_date__lte='2022-02-28')),
          mar=Count('client_id', filter=Q(enroll_date__gte='2022-03-01', enroll_date__lte='2022-03-31')),
          apr=Count('client_id', filter=Q(enroll_date__gte='2022-04-01', enroll_date__lte='2022-04-30')),
          may=Count('client_id', filter=Q(enroll_date__gte='2022-05-01', enroll_date__lte='2022-05-31')),
          jun=Count('client_id', filter=Q(enroll_date__gte='2022-06-01', enroll_date__lte='2022-06-30')),
          jul=Count('client_id', filter=Q(enroll_date__gte='2022-07-01', enroll_date__lte='2022-07-31')),
          aug=Count('client_id', filter=Q(enroll_date__gte='2022-08-01', enroll_date__lte='2022-08-31')),
          sept=Count('client_id', filter=Q(enroll_date__gte='2022-09-01', enroll_date__lte='2022-09-30')),
          oct=Count('client_id', filter=Q(enroll_date__gte='2022-10-01', enroll_date__lte='2022-10-31')),
          nov=Count('client_id', filter=Q(enroll_date__gte='2022-11-01', enroll_date__lte='2022-11-30')),
          dec=Count('client_id', filter=Q(enroll_date__gte='2022-12-01', enroll_date__lte='2022-12-31')),)\
.values_list('uploader_id__first_name', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sept', 'oct','nov', 'dec')\
.order_by('uploader_id__first_name')
elif team_parameter == 'ALL': 
    monthly_enroll = Feedback.objects\
.values('uploader_id__first_name').distinct().order_by('uploader_id__first_name')\
.annotate(jan=Count('client_id', filter=Q(enroll_date__gte='2022-01-01', enroll_date__lte='2022-01-31')),
          feb=Count('client_id', filter=Q(enroll_date__gte='2022-02-01', enroll_date__lte='2022-02-28')),
          mar=Count('client_id', filter=Q(enroll_date__gte='2022-03-01', enroll_date__lte='2022-03-31')),
          apr=Count('client_id', filter=Q(enroll_date__gte='2022-04-01', enroll_date__lte='2022-04-30')),
          may=Count('client_id', filter=Q(enroll_date__gte='2022-05-01', enroll_date__lte='2022-05-31')),
          jun=Count('client_id', filter=Q(enroll_date__gte='2022-06-01', enroll_date__lte='2022-06-30')),
          jul=Count('client_id', filter=Q(enroll_date__gte='2022-07-01', enroll_date__lte='2022-07-31')),
          aug=Count('client_id', filter=Q(enroll_date__gte='2022-08-01', enroll_date__lte='2022-08-31')),
          sept=Count('client_id', filter=Q(enroll_date__gte='2022-09-01', enroll_date__lte='2022-09-30')),
          oct=Count('client_id', filter=Q(enroll_date__gte='2022-10-01', enroll_date__lte='2022-10-31')),
          nov=Count('client_id', filter=Q(enroll_date__gte='2022-11-01', enroll_date__lte='2022-11-30')),
          dec=Count('client_id', filter=Q(enroll_date__gte='2022-12-01', enroll_date__lte='2022-12-31')),)\
.values_list('uploader_id__first_name', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sept', 'oct','nov', 'dec')\
.order_by('uploader_id__first_name')

CodePudding user response：

You could define the argument lists and keyword argument list before the if-function. And pass these to the database functions:

monthly_kwargs = {
    'jan': Count('client_id', filter=Q(enroll_date__gte='2022-01-01', enroll_date__lte='2022-01-31')),
    'feb': Count('client_id', filter=Q(enroll_date__gte='2022-02-01', enroll_date__lte='2022-02-31')),
    'mar': Count('client_id', filter=Q(enroll_date__gte='2022-03-01', enroll_date__lte='2022-03-31')),
    'apr': Count('client_id', filter=Q(enroll_date__gte='2022-04-01', enroll_date__lte='2022-04-31')),
    'may': Count('client_id', filter=Q(enroll_date__gte='2022-05-01', enroll_date__lte='2022-05-31')),
    'jun': Count('client_id', filter=Q(enroll_date__gte='2022-06-01', enroll_date__lte='2022-06-31')),
    'jul': Count('client_id', filter=Q(enroll_date__gte='2022-07-01', enroll_date__lte='2022-07-31')),
    'aug': Count('client_id', filter=Q(enroll_date__gte='2022-08-01', enroll_date__lte='2022-08-31')),
    'sept': Count('client_id', filter=Q(enroll_date__gte='2022-09-01', enroll_date__lte='2022-09-31')),
    'oct': Count('client_id', filter=Q(enroll_date__gte='2022-10-01', enroll_date__lte='2022-10-31')),
    'nov': Count('client_id', filter=Q(enroll_date__gte='2022-11-01', enroll_date__lte='2022-11-31')),
    'dec': Count('client_id', filter=Q(enroll_date__gte='2022-12-01', enroll_date__lte='2022-12-31')),
}
value_list_args = ['uploader_id__first_name', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sept', 'oct','nov', 'dec']

And than make the queries like this:

monthly_enroll = Feedback.objects\
.values('uploader_id__first_name').distinct()\
.order_by('uploader_id__first_name')\
.annotate(**monthly_kwargs)\
.values_list(*value_list_args)\
.order_by('uploader_id__first_name')