左加入Django ORM
左加入Django ORM
提问于 2014-01-22 00:21:07
我有以下几种型号：
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)我想查询所有没有志愿者的部门。我可以使用以下查询来做到这一点：
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;是否有一种更像django的方法来这样做，或者我应该只使用原始sql？
回答 4
Stack Overflow用户
回答已采纳
发布于 2014-01-22 00:54:28
您可以通过跟踪查找中的反向关系来做到这一点。
>>> 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_departmentvolunteer"."id" IS NULL下面是关于“跨多值关系”查询的文档：https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-multi-valued-relationships
Stack Overflow用户
发布于 2016-06-07 19:50:24
对我来说，需要定制连接模型，这些模型有隐式字段(no有ForeignKey关系)
在django 1.9上对我有用。
但更像是在拐杖上
如果有人有更优雅的解决方案，请与人分享
from django.db.models.sql.datastructures import Join
from django.db.models.fields.related import ForeignObject
from django.db.models.options import Options
from myapp.models import Ace
from myapp.models import Subject
jf = ForeignObject(
to=Subject,
on_delete=lambda: x,
from_fields=[None],
to_fields=[None],
rel=None,
related_name=None
)
jf.opts = Options(Ace._meta)
jf.opts.model = Ace
jf.get_joining_columns = lambda: (("subj", "name"),)
j=Join(
Subject._meta.db_table, Ace._meta.db_table,
'T1', "LEFT JOIN", jf, True)
q=Ace.objects.filter(version=296)
q.query.join(j)
print q.query结果：
SELECT
`ace`.`id`,
`ace`.`version_id`,
`ace`.`obj`,
`ace`.`subj`,
`ace`.`ACE_Type`,
`ace`.`ACE_Inheritance`,
`ace`.`ACE_Rights`
FROM `ace`
LEFT OUTER JOIN `core_subject`
ON (`ace`.`subj` = `core_subject`.`name`)
WHERE `ace`.`version_id` = 296这里的示例使用附加条件和设置表别名(但它似乎是拐杖)
def join_to(self, table1, table2, field1, field2, queryset, alias=''):
"""
table1 base
"""
# here you can set complex clause for join
def extra_join_cond(where_class, alias, related_alias):
if (alias, related_alias) == ('[sys].[columns]',
'[sys].[database_permissions]'):
where = '[sys].[columns].[column_id] = ' \
'[sys].[database_permissions].[minor_id]'
children = [ExtraWhere([where], ())]
wh = where_class(children)
return wh
return None
dpj = ForeignObject(
to=table2,
on_delete=lambda: None,
from_fields=[None],
to_fields=[None],
rel=None,
related_name=None
)
dpj.opts = Options(table1._meta)
dpj.opts.model = table1
dpj.get_joining_columns = lambda: ((field1, field2),)
dpj.get_extra_restriction = extra_join_cond
dj = Join(
table2._meta.db_table, table1._meta.db_table,
'T', "LEFT JOIN", dpj, True)
ac = queryset._clone()
ac.query.join(dj)
# hook for set alias
alias and setattr(dj, 'table_alias', alias)
return ac我用它
# how it use:
from django.db.models.expressions import Col
q = Something.objects \
.filter(type__in=["'S'", "'U'", "'G'"]) \
.exclude(name__in=("'sys'", "'INFORMATION_SCHEMA'")) \
.annotate(
... some annotation fields
class_= Col(Permissions._meta.db_table,
Permissions._meta.get_field('field_name'),
output_field=IntegerField()),
Grant=Col(
'T10',
Principals._meta.get_field('name'),
output_field=CharField()),
).values('Grant')
ac = self.join_to(Principals, ServerPrincipals, 'sid', 'sid', q)
# here invoke "extra_join_cond" of function "join_to"
ac = self.join_to(Permissions, Columns, 'major_id', 'object_id', ac)
# here use alias table
ac = self.join_to(Permissions, Principals, 'grantor_id', 'principal_id', ac, 'T10') # T10 is aliassql将是
SELECT
T10.name AS Grant
FROM sys.principals
LEFT OUTER JOIN sys.server_principals
ON (sys.principals.sid = sys.server_principals.sid)
LEFT OUTER JOIN sys.columns
ON (sys.permissions.major_id = sys.columns.object_id
AND (
(sys.columns.column_id = sys.permissions.minor_id))
)
LEFT OUTER JOIN sys.principals T10
ON (sys.permissions.grantor_id = T10.principal_id)Stack Overflow用户
发布于 2014-01-22 00:55:19
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21271835
复制相关文章
相似问题
腾讯云开发者
