Prefetch_related с фильтром для m2m

Я пытаюсь построить набор запросов для поиска кандидатов, которые подали заявку на работу_типа, знают несколько языков и хотят работать в некоторых зонах. Я могу выбрать кандидатов на основе типа_работы_и_языков, но мой запрос не фильтрует по зонам.
. :

{
    "jobtype_id": 15,
    "languages": [21,83],
    "zones": [1,2,4]
} 

Это мой набор запросов:

candidates = Candidate.objects.all().prefetch_related('job_types', 'languages').filter(
            job_types__id=job_type_id, languages__id__in=filter_languages).prefetch_related(
            Prefetch('job_types__candidate_job_type_zone',
                     queryset=CandidateJobTypeZone.objects.all().filter(zone_id__in=filter_zones)),
            )

Вот сгенерированный SQL (я хочу внутреннее присоединение к таблице candidate_job_type_zone и фильтр по id зоны):

SELECT `candidates`.`name`, `candidates`.`firstname`, `candidates`.`lastname`, `candidates`.`phone_number`, `candidates`.`secondary_phone_number`, `candidates`.`profession`, `candidates`.`own_car`, `candidates`.`driver_licence`, `candidates`.`location`, `candidates`.`document_type`, `candidates`.`document_id`, `candidates`.`work_permit`, `candidates`.`prefer_language`, `candidates`.`is_promoted`, `candidates`.`last_promoted_payment_date`, `candidates`.`is_active_job_search`, `candidates`.`last_active_job_search_date`, `candidates`.`created_at`, `candidates`.`updated_at`, `candidates`.`extra_data`, `candidates`.`user_id`, `candidates`.`nationality_id` FROM `candidates` INNER JOIN `candidates_jobs_types` ON (`candidates`.`user_id` = `candidates_jobs_types`.`candidate_id`) INNER JOIN `candidates_languages` ON (`candidates`.`user_id` = `candidates_languages`.`candidate_id`) WHERE (`candidates_jobs_types`.`job_type_id` = 15 AND `candidates_languages`.`language_id` IN (21, 83))

Это могут быть модели:

class Candidate(models.Model):
         ... 
    
        # relations
        user = models.OneToOneField(
            User, on_delete=models.CASCADE, null=False, verbose_name="Usuario relacionado", 
        primary_key=True)
        job_types = models.ManyToManyField('JobType', through="CandidateJobType")
        languages = models.ManyToManyField(Language, through="CandidateLanguage")  
    
class CandidateJobType(models.Model):
       
        candidate = models.ForeignKey(
            Candidate, verbose_name="Candidato", on_delete=models.CASCADE)
        job_type = models.ForeignKey(
            JobType, verbose_name="Puesto de trabajo", on_delete=models.CASCADE, 
        related_name='job_types')
    
        extra_data = models.JSONField(verbose_name="Datos extras", null=True)
        zones = models.ManyToManyField(Zones, through="CandidateJobTypeZone")
        is_active = models.BooleanField(verbose_name="Is active", null=True, default=True)
    
        class Meta:
            db_table = "candidates_jobs_types"
            unique_together = ('candidate', 'job_type')
    
class CandidateJobTypeZone(models.Model):
      
        candidate_job_type = models.ForeignKey(
            CandidateJobType, on_delete=models.CASCADE, related_name='candidate_job_type_zone')
    
        zone = models.ForeignKey(Zones, on_delete=models.CASCADE)
    
        created_at = models.DateTimeField(auto_now_add=True)
        updated_at = models.DateTimeField(auto_now=True)
    
        class Meta:
            db_table = "candidate_jobs_types_zones"
    
class Zones(models.Model):
    
        name_es = models.CharField(
            max_length=50, null=False, blank=False, verbose_name="Nombre(ES)")
        name_en = models.CharField(
            max_length=50, null=False, blank=False, verbose_name="Nombre(EN)")
        slug = models.CharField(max_length=70, null=False, blank=False, unique=True)
        is_active = models.BooleanField(
            verbose_name="Activa", null=False, default=True)
        created_at = models.DateTimeField(auto_now_add=True)
        updated_at = models.DateTimeField(auto_now=True)
    
        class Meta:
            verbose_name_plural = "Zonas"
            verbose_name = "Zona"
            db_table = "zones"
    
class CandidateLanguage(models.Model):
        candidate = models.ForeignKey(Candidate, on_delete=models.CASCADE)
        language = models.ForeignKey(Language, on_delete=models.CASCADE)
        level = models.CharField(max_length=20, choices=level_choices)
        created_at = models.DateTimeField(auto_now_add=True)
        updated_at = models.DateTimeField(auto_now=True)
    
        class Meta:
            unique_together = ('candidate', 'language',)
            db_table = "candidates_languages"

Вернуться на верх