how to perform django queryset in more efficient way to get following response?

Question

here is my model fields:

staff = models.ForeignKey(StaffProfile, on_delete = models.SET_NULL, blank=True,null = True)
created_at = models.DateTimeField(auto_now_add=True)
category = models.CharField(max_length = 250,choices = EventStatusChoice.choices,default = EventStatusChoice.PROGRESS_NOTES)
summary = models.CharField(max_length=200, blank=True, null=True)

Event category choices are:

class EventStatusChoice(models.TextChoices):
INJURIES = 'injury','Injuries'
FEEDBACKS = 'feedback','Feedbacks'
ENQUIRIES = 'enquiry','Enquiries'
INCIDENTS = 'incident','Incidents'
PROGRESS_NOTES = 'note','Progress Notes'
WARNING = "warning", "Warning"

I want a response something like following:

 "data": [
    {
        "staff": "admin",
        "injury": 0,
        "feedback": 2,
        "enquiry": 0,
        "incident": 1,
        "note": 0,
        "warning": 0
    },
    {
        "staff": "tutaketuw",
        "injury": 0,
        "feedback": 0,
        "enquiry": 0,
        "incident": 1,
        "note": 0,
        "warning": 0
    },]

I did it in my own way, and its working good. Following is my way:

def get_staff_summary(self,start = None,end = None,search = None):
    qs = EventModel.objects.get_distinct_staff(search)
    data_list = []
    for i in qs:
        data = {}
        data['staff'] = i['staff__staff__username']
        for j in EventStatusChoice.choices:
            if start != None and end != None:
                data[f'{j[0]}'] = EventModel.objects.filter(category = j[0],staff__staff__username = i['staff__staff__username'],created_at__date__gte = start,created_at__date__lte = end).count()
            else:
                data[f'{j[0]}'] = EventModel.objects.filter(category = j[0],staff__staff__username = i['staff__staff__username']).count()
        data_list.append(data)
    return data_list

Now, I want to do it in much more proper and efficient way. Can anybody please help to get above response(or any similar type of response) by using only Django queryset. Basically I want a list of all staffs with their categories and count.

score 0 · Answer 1 · answered Oct 19 '22 at 11:51

It would be clearer if you had provided all your models but i guess something like

annotation_dict = {}
for event_choice in EventStatusChoice.choices:
    annotation_dict.update({event_choice[0]: Count("eventmodel", filter=Q(category=eventchoice[0]))})
Staff.objects.annotate(**annotation_dict)

how to perform django queryset in more efficient way to get following response?

1 Answers1