LEFT JOIN Django ORM

Question

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?

score 53 · Accepted Answer · edited Nov 09 '22 at 14:16

53

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_departmentvolunteer"."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

edited Nov 09 '22 at 14:16

djvg

11,722
5
72
103

answered Jan 22 '14 at 00:54

Mark Lavin

24,664
5
76
70

Thanks! I'll compare your solution with mine. – hanleyhansen Jan 22 '14 at 00:56
1

Is your query checking against the id field in DepartmentVolunteer? Or will Django make the relationship and check against the department_id field? – hanleyhansen Jan 22 '14 at 01:00
3

It's checking against the `department` column in the `DepartmentVolunteer` which is a FK to `Departement` and hence matches on its ID. – Mark Lavin Jan 22 '14 at 01:06
2

This is an outer join. What if you don't want the right join matches in there too...? – CpILL Jul 13 '14 at 23:48
3

In mine, the query is an `INNER JOIN`. I am using `django 1.11.20`. Any thoughts on how to do a left join? I need an `INNER JOIN` with some `LEFT JOIN`. I've been trying to search it but not able to find a solution. – alltej Aug 09 '19 at 13:22

madjardi · Answer 2 · 2022-01-25T09:11:25.170

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)

excellent! You made my day sir. For adding a little complement, I already had the Join created with my query, so my solution was to override it ;)`queryset.query.alias_map['my_outer_table'].join_field.get_extra_restriction = extra_join_cond` — Guillaume Cisco, Sep 06 '16 at 16:07
Thanks @madjardi. Your answer helped me in solving this problem: http://stackoverflow.com/a/42816689/2367394 — Debanshu Kundu, Mar 15 '17 at 17:28

score 10 · Answer 3 · answered Jan 22 '14 at 00:55

10

This seems to be working:

Department.objects.filter(departmentvolunteer__department__isnull=True)

See docs for more details.

answered Jan 22 '14 at 00:55

hanleyhansen

6,304
8
37
73

madjardi · Answer 4 · 2018-03-16T08:43:54.630

for create custom join by OR

def get_queryset(self):
    qs = super(AceViewSet, self).get_queryset()
    qs = qs.select_related('xxx')
    # construct all tables and the join dependence
    qs.query.__str__()

    qs.query.alias_map['xx_subject'].join_cols = (('xxx_id', 'uid'), ('xxx_id', 'ad_subject_id'))
    qs.query.alias_map['xx_subject'].as_sql = partial(self.as_sql, qs.query.alias_map['xx_subject'])
    return qs

@staticmethod
def as_sql(self, compiler, connection):
    sql, params = Join.as_sql(self, compiler, connection)
    or_sql = sql.replace("AND", "OR")
    return or_sql, params

FROM "xx_ace"
  LEFT OUTER JOIN "xx_subject"
    ON ("xx_ace"."xxx_id" = "xx_subject"."uid" OR "xx_ace"."xxx_id" = "xx_subject"."ad_subject_id")

LEFT JOIN Django ORM

4 Answers4

Linked