LEFT JOIN Django ORM

Question

I have the following models:

class Volunteer(models.Model):     first_name = models.CharField(max_length=50L)     last_name = models.CharField(max_length=50L)         email = models.CharField(max_length=50L)     gender = models.CharField(max_length=1, choices=GENDER_CHOICES)   class Department(models.Model):     name = models.CharField(max_length=50L, unique=True)     overseer = models.ForeignKey(Volunteer, blank=True, null=True)     location = models.CharField(max_length=100L, null=True)   class DepartmentVolunteer(models.Model):     volunteer = models.ForeignKey(Volunteer)     department = models.ForeignKey(Department)     assistant = models.BooleanField(default=False)     keyman = models.BooleanField(default=False)     captain = models.BooleanField(default=False)     location = models.CharField(max_length=100L, blank=True, null=True) 

I want to query for all departments that have no volunteers assigned to them. I can do so using the following query:

SELECT      d.name  FROM        vsp_department AS d LEFT JOIN vsp_departmentvolunteer AS dv ON d.id = dv.department_id   WHERE     dv.department_id IS NULL; 

Is there a more django-like way of doing this or should i just go with raw sql?

Answer 1

You can do this by following the backwards relation in the lookup.

>>> qs = Department.objects.filter(departmentvolunteer__isnull=True).values_list('name', flat=True) >>> print(qs.query) SELECT "app_department"."name" FROM "app_department" LEFT OUTER JOIN "app_departmentvolunteer" ON ( "app_department"."id" = "app_departmentvolunteer"."department_id" ) WHERE "app_epartmentvolunteer"."id" IS NULL 

Here are the docs on queries "Spanning multi-valued relationships": https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-multi-valued-relationships