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?

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.

Solution 4

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")

View more solutions

Comments

[hanleyhansen]

Thanks! I'll compare your solution with mine.

[hanleyhansen]

Is your query checking against the id field in DepartmentVolunteer? Or will Django make the relationship and check against the department_id field?

[Mark Lavin]

It's checking against the department column in the DepartmentVolunteer which is a FK to Departement and hence matches on its ID.

[hanleyhansen]

Gotcha. Makes sense. Thank you.

[CpILL]

This is an outer join. What if you don't want the right join matches in there too...?

[Guillaume Cisco]

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

[Debanshu Kundu]

Thanks @madjardi. Your answer helped me in solving this problem: stackoverflow.com/a/42816689/2367394

[alltej]

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.