LEFT JOIN Django ORM
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?
Solution 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
Solution 2:
To me were need custom join models, that have implicit fields (no have ForeignKey relation)
it work to me on django 1.9.
but it more seem on the crutch
If someone have more elegant solution please share for people
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
result:
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
here example of use with additional condition and set table alias(but it seem as crutch)
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
i use it by
# 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 alias
sql'll be
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)
Solution 3:
This seems to be working:
Department.objects.filter(departmentvolunteer__department__isnull=True)
See docs for more details.