How can i filter by many-to-many field in other table Django ORM?

Question

My models:

class Job:
    id = models.UUIDField()
    name = models.CharField()
    category = models.ForeignKey(Category)


class JobReviewers:
    user_id = models.UUIDField()
    name = models.CharField()
    categories = models.ManyToManyField(Category, related_name='reviewers')

class Category:
    id = models.UUIDField()

I want to filter all job's where user is reviewer for job with name and category

user_id = uuid.uuid4()
all_jobs = Job.objects.all()
jobs_where_user_is_reviewer = ...

If it'll be filter just by name without category i can write this:

job_names_where_user_reviewer = JobReviewers.objects.filter(user_id=user_id).values('name', flat=True)
jobs_where_user_is_reviewer = all_jobs.filter(name__in=job_names_where_user_reviewer)

How can i do this with categories m2m field?

Answer 1

Given I understand it correctly, you want to get Jobs for which the name matches, and the category is in the categories of a JobReviewers, where user_id referes to a given user_id.

We can do this with an Exists subquery ^[Django-doc]:

from django.db.models import Exists, OuterRef

Job.objects.filter(
    Exists(
        JobReviewers.objects.filter(
            name=OuterRef('name'), categories=OuterRef('category'), user_id=user_id
        )
    )
)

Answer 2

Try:

category_id = uuid.uuid4()
my_jobs = all_jobs.filter(name__in=job_names_where_user_reviewer, category__id=category_id)