Django reduce amount of queries (M2M relation with through model)

Question

I would like to reduce the amount of similar queries. Here are my models:

class Skill(models.Model):
    name = models.TextField()

class Employee(models.Model):

    firstname = models.TextField()
    skills = models.ManyToManyField(Skill, through='SkillStatus')

    def skills_percentage(self):
        completed = 0
        total = 0

        for skill in self.skills.all().prefetch_related("skillstatus_set__employee"):
            for item in skill.skillstatus_set.all():
                if item.employee.firstname == self.firstname:
                    total += 1
                    if item.status:
                        completed += 1
        try:
            percentage = round((completed / total * 100), 2)
        except ZeroDivisionError:
            percentage = 0.0
        return f"{percentage} %"

class SkillStatus(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    skill = models.ForeignKey(Skill, on_delete=models.CASCADE)
    status = models.BooleanField(default=False)

My main problen is related to method skills_percentage, I make too many queries while calculating mentioned value. I have already improved situation a little bit with prefetch_related, but there are still extra queries in Django Debug Toolbar. What else can be done here?

I have tried to play with different combinations of select_related and prefetch_related. I thought about other options to calculate skills_percentage but they also required to many queries...

Thanks in advance.

ruddra · Accepted Answer · 2023-02-20T10:00:16.043

0

You can try like this:

from django.db.models import Count, When, Case, Cast, FloatField

employees = Employee.objects.annotate(
        total=Count('skills',distinct=True),
        completed = Count('skills', filter=Q(skillstatus__status=True),distinct=True)
    ).annotate(
        percentage= Case(
            When(total=0, then=0.0),
            default=(Cast(
                F('completed')*100 / F('total'),
                output_field=FloatField()
            )
        )
    )
)

# usage

for employee in employees:
    print(employee.percentage)

# or 

employees.values('firstname', 'percentage')

Here I am counting skills twice, once without any condition for total and second time with a filter condition. Then I am annotating the division of completed/total value with the original queryset by casting it as FloatField.

edited Feb 20 '23 at 10:00

answered Feb 17 '23 at 11:51

ruddra

50,746
7
78
101

Thank you very much. It reduced number of queries a little bit, but "employees" itself is a queryset (not number). Or am I doing something wrong? – Антон Ласько Feb 17 '23 at 16:44
you can access those values via loop or values queryset. I will add some examples in the answer – ruddra Feb 17 '23 at 16:47
Hello, thanks a lot for correction. It eliminated extra queries but, unfortunately, the result is not correct for all my employees. Looks like percentage is not calculated properly (it can be either 1 or 0). The problem is somewhere in "completed" value... I have already changed "filter=Q(skills__status=True)" to "filter=Q(skills__skillstatus__status=True)" but there is still something – Антон Ласько Feb 20 '23 at 08:55
Fixed it with minor modification)employees = Employee.objects.annotate( total=Count('skillstatus', distinct=True), completed=Count('skillstatus', filter=Q(skillstatus__status=True), distinct=True), ).annotate( percentage=Case( When(total=0, then=0.0), default=(Cast( F('completed') * 100 / F('total'), output_field=FloatField() ) ) ) ) – Антон Ласько Feb 20 '23 at 09:52

Django reduce amount of queries (M2M relation with through model)

1 Answers1