Django Queryset with multiple joins and filtering

Question

I need a list of jobs joined with houses joined with owners

Results row should contain: job_id, job_worker_role1, job_worker_role2, job_worker_role3, house_id, *owner_name, owner_status_name, owner_assigned_user

Given that there is a many to many relationship between houses and owners it is acceptable to have multiple result rows for each job, with each owner.

class Worker:
    name = models.CharField()

class OwnerStatus:
    name = models.CharField()

class Owner:
    name = models.CharField()
    status = models.ForeignKey(OwnerStatus, related_name='owners')
    assigned_worker = models.ForeignKey(Worker, related_name='assigned_user_owner')

class House:
    owners = models.ManyToManyField(Owner, related_name='assets')

class Job:
    house = models.ForeignKey(House, related_name='jobs')
    worker_role1 = models.ForeignKey(Worker)
    worker_role2 = models.ForeignKey(Worker)
    worker_role3 = models.ForeignKey(Worker)
    updated_at = models.DateTimeField(auto_now=True)

Also, I need to filter by:

• owner status name or owner status id
• owner name as string
• owner assigned worker name as string

I am using Django 4.0.1 with postgres

Answer 1

You can filter with the following:

Owner status name or owner status id:

from django.db.models import Q
filterd_jobs = Job.objects.filter(Q(house__owners__status__name='status_name') | Q(house__owners__status_id='status_id'))

Owner name as string

filterd_jobs = Job.objects.fitler(house__owners__name='owner_name')

owner assigned worker name as string

filterd_jobs = Job.objects.fitler(house__owners__assigned_worker__name='assigned_worker_name')