Django ORM filter SUM different related objects on a loop

user_7341067 0
Viewed 104

I have the following models:

    class Developer(models.Model):
        name = models.CharField(max_length=100)

    class Skill(models.Model):
        code = models.CharField(max_length=30)

    class Experience(models.Model):
        date_from = models.DateField(blank=True, null=True)
        date_to = models.DateField(blank=True, null=True)
        developer = models.ForeignKey(Developer, related_name='experience', 
        on_delete=models.CASCADE)
    
    class SkillExperience(models.Model):
        skill = models.ForeignKey(Skill, on_delete=models.CASCADE, related_name='skill_experience')
        experience = models.ForeignKey(Experience, on_delete=models.CASCADE, related_name='skill_experience')
        years_experience = models.IntegerField()

I know that for a single query, to get the Devs with at least 5 years of experience in Python, I could do:

Developer.objects.filter(
    experience__skill_experience__skill__code='Python'
).annotate(
    total_years=Sum('experience__skill_experience__years_experience')
).filter(
    total_years__gte=5
)

But how to proceed if I'm on a loop of skills and I need to check an OR, so let's say looping this array: [{code: 'python', years_experience: 5}, {code: 'node', years_experience: 2}] and I need to return Devs who have either 5 years xp with python OR 2 years xp with node?

django-orm django python
1 Answers

I think the simplest way will be to determine the sums of all skills and then check if at least one of these satisfies the constraints, so:

# since Django-2.0

from django.db.models import Q, Sum

# sample data
data = [
    {code: 'python', years_experience: 5},
    {code: 'node', years_experience: 2}
]

annotates = {
    f'total_years_{i}': Sum(
        'experience__skill_experience__years_experience',
        filter=Q(experience__skill_experience__skill__code=di['code'])
    ) for i, di in enumerate(data)
}

qs = Q(
    *[(f'total_years_{i}__gte', di['years_experience']) for i, di in enumerate(data)],
    _connector=Q.OR
)

developers = Developer.objects.annotate(
    **annotates
).filter(
    qs    
)

As a bonus, the Developer objects in developers will have attributes like .total_years_0, .total_years_1, etc. for each skill in the list that contains the sum of the years of that skill.

user_675790
Related