Python 左加入 Django ORM

LEFT JOIN Django ORM

提问by hanleyhansen

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 
    vsp_department.name 
FROM   
    vsp_department 
LEFT JOIN vsp_departmentvolunteer ON vsp_department.id = vsp_departmentvolunteer.department_id  
WHERE
    vsp_departmentvolunteer.department_id IS NULL;

Is there a more django-like way of doing this or should i just go with raw sql?

有没有更像 django 的方式来做到这一点，还是我应该使用原始 sql？

采纳答案by Mark Lavin

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

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

回答by hanleyhansen

This seems to be working:

这似乎有效：

Department.objects.filter(departmentvolunteer__department__isnull=True)

See docsfor more details.

有关更多详细信息，请参阅文档。

回答by madjardi

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

对我来说，需要自定义连接模型，它具有
在 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

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

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)

回答by madjardi

for create custom join by OR

用于通过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")