CODEWITHSUNDEEP
pythondjangodjango-orm
Luc
Luc

Reputation: 412

Django ORM filter SUM different related objects

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 need a query to retrieve Developers that have at years_experience of at least 5 in skill code 'python', for example. However I can't simply do Developer.objects.filter(skill_experience__years_experience__gte=5) because I could have two experiences in Python but one 3 years and another one 2 years and that won't show in the query above. So I need to sum up all the years_experience that are from skill__code="Python" and evaluate it. Is there some way to do it with a single query?

Upvotes: 1

Views: 37

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476574

Yes, you can use .annotate(…) [Django-doc] to sum up the years_experience and then filter on the total_years:

from django.db.models import Sum

Developer.objects.filter(
    # filter on Python skills (so exclude for example Java)
    experience__skill_experience__skill__code='Python'
).annotate(
    # sum up these years
    total_years=Sum('experience__skill_experience__years_experience')
).filter(
    # check if the total is at least five
    total_years__gte=5
)

Upvotes: 1

Related Questions