How to write complex filter queries on M2M models in Django?

Question

I have an app matching candidates (Candidate) with jobs (Job). For both of these models I have an m2m relationship with a Language. A language can be required for a specific job.

class JobLanguage(models.Model):

    language = models.ForeignKey(Language, on_delete=models.CASCADE)
    job = models.ForeignKey(Job, related_name='languages', on_delete=models.CASCADE)
    is_mandatory = models.BooleanField(default=False)

And a candidate speaks a language with a certain level of "mastery".

class CandidateLanguage(models.Model):
    language = models.ForeignKey(CandidateComponentLanguage, on_delete=models.CASCADE)
    candidate = models.ForeignKey(Candidate, on_delete=models.CASCADE, related_name='languages')
    mastery = models.PositiveSmallIntegerField(blank=True, choices=MasteryLevel.choices, null=True)

Now in my matching algorithm I need at some point to filter candidates based on the languages required for a specific job. Let's say I have list of mandatory languages for a job : job_mandatory_languages_ids = [1, 2]. How would you write in the most efficient way a method that only returns candidates who master (meaning mastery=1) at least all the languages in this list ? I would like to avoid having to iterate on each candidate to remove them from the list and just use something like filter(Q())

This obviously does not work but it is an idea of what I want to achieve.

def filter_on_languages(candidates, job_mandatory_languages_ids):
    return candidates.filter(languages__language__contains=job_mandatory_languages_ids, languages__language__mastery=1)

Thank you :)

Answer 1

" I would like to avoid having to iterate on each candidate to remove them from the list and just use something like filter(Q())"

And what about doing a thing like this :

from django.db.models import Q

def filter_on_languages(candidates, job_mandatory_languages_ids):
    q_objects = Q()
    for language_id in job_mandatory_languages_ids:
        q_objects &= Q(languages__language__id=language_id, languages__mastery=1)
    return candidates.filter(q_objects)

This function takes a queryset of candidates and a list of mandatory language ids for a job, and returns a filtered queryset of candidates who master at least all the mandatory languages.

Like this you can filtered out your candidates without parsing them in a for loop.