LEFT JOIN Django ORM

49

我有以下模型:

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)

我想查询所有没有志愿者分配的部门。我可以使用以下查询来实现:

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;

有更像 Django 的方法来做这件事吗?还是我应该使用原始 SQL?

4个回答

54
你可以通过按照查找中的反向关系来实现这一点。
>>> 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

这是关于查询“跨多值关系”的文档:https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-multi-valued-relationships


谢谢!我会把你的解决方案和我的进行比较。 - hanleyhansen
1
你的查询是否针对DepartmentVolunteer中的id字段进行检查?还是Django会建立关系并针对department_id字段进行检查? - hanleyhansen
3
它正在检查DepartmentVolunteer中的department列,该列是指向Departement的外键,因此匹配其ID。 - Mark Lavin
明白了,有道理。谢谢。 - hanleyhansen
2
这是一个外连接。如果您不想在其中包含右连接匹配怎么办...? - CpILL
3
在我的查询中,使用的是"INNER JOIN"。我正在使用"Django 1.11.20"版本。你有什么想法可以实现"LEFT JOIN"吗?我需要将某些"LEFT JOIN"与"INNER JOIN"结合起来使用。我一直在尝试搜索解决方案,但没有找到。 - alltej

26

我需要自定义连接模型,其中有隐式字段(没有外键关系)。

在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
    """
    # 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

我使用它通过

# 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将会是

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)

1
太棒了!你让我感到非常开心。为了再加一点赞美,我已经在我的查询中创建了Join,所以我的解决方案是覆盖它;queryset.query.alias_map ['my_outer_table'] .join_field.get_extra_restriction = extra_join_cond - Guillaume Cisco
1
感谢@madjardi。你的回答帮助我解决了这个问题:https://dev59.com/oWAh5IYBdhLWcg3wBfeM#42816689 - Debanshu Kundu

10

这似乎正在发挥作用:

Department.objects.filter(departmentvolunteer__department__isnull=True)

请查看文档以获取更多详细信息。


1
创建自定义联接使用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")

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接