我需要自定义连接模型,其中有隐式字段(没有外键关系)。
在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
"""
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)
alias and setattr(dj, 'table_alias', alias)
return ac
我使用它通过
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)
ac = self.join_to(Permissions, Columns, 'major_id', 'object_id', ac)
ac = self.join_to(Permissions, Principals, 'grantor_id', 'principal_id', ac, 'T10')
SQL将会是
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)
DepartmentVolunteer
中的department
列,该列是指向Departement
的外键,因此匹配其ID。 - Mark Lavin