LEFT JOIN Django ORM

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

To me were need custom join models, that have implicit fields
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 Joinfrom django.db.models.fields.related import ForeignObjectfrom django.db.models.options import Optionsfrom myapp.models import Acefrom myapp.models import Subjectjf = 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 = Acejf.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 GrantFROM 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)

This seems to be working:

Department.objects.filter(departmentvolunteer__department__isnull=True)

See docs for more details.