左键加入Django ORM [英] LEFT JOIN Django ORM

问题描述

我有以下模型：

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 
    vsp_department.name 
FROM   
    vsp_department 
LEFT JOIN vsp_departmentvolunteer ON vsp_department.id = vsp_departmentvolunteer.department_id  
WHERE
    vsp_departmentvolunteer.department_id IS NULL;

有没有更多django的方式这样做，还是应该用原始的sql？ / p>

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

推荐答案

您可以通过跟踪查找中的向后关系来执行此操作。

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

以下是有关查询的文档跨越多重关系： https：//docs.djangoproject。 com / en / stable / topics / db / queries /＃spanning-multi-valued-relationships

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

这篇关于左键加入Django ORM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！