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.

Answer 1

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.