左加入 Django ORM [英] LEFT JOIN Django ORM
本文介绍了左加入 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)性别=models.CharField(max_length=1,选择=GENDER_CHOICES)班级部门(models.Model):name = models.CharField(max_length=50L, unique=True)监督者 = models.ForeignKey(志愿者,空白=真,空=真)location = models.CharField(max_length=100L, null=True)类部门志愿者(models.Model):志愿者=models.ForeignKey(志愿者)部门=models.ForeignKey(部门)助手 = 模型.BooleanField(默认=假)keyman = models.BooleanField(默认=假)船长 = models.BooleanField(默认=假)location = models.CharField(max_length=100L, blank=True, null=True)
我想查询所有没有分配志愿者的部门.我可以使用以下查询来做到这一点:
SELECTd. 姓名从vsp_department AS d左加入 vsp_departmentvolunteer AS dvON d.id = dv.department_id在哪里dv.department_id 为空;
是否有更像 django 的方式来做这件事,还是我应该使用原始 sql?
解决方案
您可以通过在查找中遵循向后关系来做到这一点.
<预><代码>>>>qs = Department.objects.filter(departmentvolunteer__isnull=True).values_list('name', flat=True)>>>打印(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" 为空以下是有关跨越多值关系"查询的文档:https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-multi-valued-relationships
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?
解决方案
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
这篇关于左加入 Django ORM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文