Django ORM.联接子查询 [英] Django ORM. Joining subquery

问题描述

我有一个表，其中包含一些网站的列表和一个包含其统计信息的表.

I have a table which contains list of some web sites and a table with statistics of them.

class Site(models.Model):
    domain_name = models.CharField(
        max_length=256,
        unique=True,
    )


class Stats(models.Model):
    date = models.DateField()
    site = models.ForeignKey('Site')
    google_pr = models.PositiveIntegerField()

    class Meta:
        unique_together = ('site', 'date')

我想查看一个具体日期的所有站点和统计信息.如果该日期的统计记录不存在，则选择内容必须仅包含网站.

I want to see all sites and statistics for a concrete date. If a stats record for the date doesn't exist, then the selection must contain only site.

如果我使用:

Site.objects.filter(stats__date=my_date)

我将不会获得在stats表中没有my_date记录的站点.因为在这种情况下，SQL查询将如下所示:

I will not get sites which have no records for my_date in stats table. Because in this case the SQL query will be like the following:

SELECT *
FROM site
LEFT OUTER JOIN stats ON site.id = stats.site_id
WHERE stats.date = 'my_date'

查询条件将排除具有NULL日期的记录，并且没有统计信息的网站将不包括在选择中.

The query condition will exclude records with NULL-dates and sites without stats will be not included to the selection.

就我而言，我需要联接统计表，该表已按日期进行了过滤:

In my case I need join stats table, which has already been filtered by date:

SELECT *
FROM site
LEFT OUTER JOIN
  (SELECT *
   FROM stats
   WHERE stats.date = 'my-date') AS stats
ON site.id = stats.site_id

如何将该查询转换为Django ORM?

How can I translate this query to Django ORM?

谢谢.

推荐答案

我遇到了类似的问题，并编写了以下实用程序函数，用于使用Django ORM在子查询集上添加左外部联接.

I had a similar problem and wrote the following utility function for adding left outer join on a subqueryset using Django ORM.

该util源自使用Django ORM将自定义左外部联接添加到另一个表(不是子查询)的解决方案.这是该解决方案: https://stackoverflow.com/a/37688104/2367394

The util is derived from a solution given to add custom left outer join to another table (not subquery) using Django ORM. Here is that solution: https://stackoverflow.com/a/37688104/2367394

以下是该实用程序和所有相关代码:

Following is the util and all related code:

from django.db.models.fields.related import ForeignObject
from django.db.models.options import Options
from django.db.models.sql.where import ExtraWhere
from django.db.models.sql.datastructures import Join


class CustomJoin(Join):
    def __init__(self, subquery, subquery_params, parent_alias, table_alias, join_type, join_field, nullable):
        self.subquery_params = subquery_params
        super(CustomJoin, self).__init__(subquery, parent_alias, table_alias, join_type, join_field, nullable)

    def as_sql(self, compiler, connection):
        """
        Generates the full
        LEFT OUTER JOIN (somequery) alias ON alias.somecol = othertable.othercol, params
        clause for this join.
        """
        params = []
        sql = []
        alias_str = '' if self.table_alias == self.table_name else (' %s' % self.table_alias)
        params.extend(self.subquery_params)
        qn = compiler.quote_name_unless_alias
        qn2 = connection.ops.quote_name
        sql.append('%s (%s)%s ON (' % (self.join_type, self.table_name, alias_str))
        for index, (lhs_col, rhs_col) in enumerate(self.join_cols):
            if index != 0:
                sql.append(' AND ')
            sql.append('%s.%s = %s.%s' % (
                qn(self.parent_alias),
                qn2(lhs_col),
                qn(self.table_alias),
                qn2(rhs_col),
            ))
        extra_cond = self.join_field.get_extra_restriction(
            compiler.query.where_class, self.table_alias, self.parent_alias)
        if extra_cond:
            extra_sql, extra_params = compiler.compile(extra_cond)
            extra_sql = 'AND (%s)' % extra_sql
            params.extend(extra_params)
            sql.append('%s' % extra_sql)
        sql.append(')')
        return ' '.join(sql), params

def join_to(table, subquery, table_field, subquery_field, queryset, alias):
    """
    Add a join on `subquery` to `queryset` (having table `table`).
    """
    # 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], ())]
            return where_class(children)
        return None
    foreign_object = ForeignObject(to=subquery, from_fields=[None], to_fields=[None], rel=None)
    foreign_object.opts = Options(table._meta)
    foreign_object.opts.model = table
    foreign_object.get_joining_columns = lambda: ((table_field, subquery_field),)
    foreign_object.get_extra_restriction = extra_join_cond
    subquery_sql, subquery_params = subquery.query.sql_with_params()
    join = CustomJoin(
        subquery_sql, subquery_params, table._meta.db_table,
        alias, "LEFT JOIN", foreign_object, True)

    queryset.query.join(join)

    # hook for set alias
    join.table_alias = alias
    queryset.query.external_aliases.add(alias)

    return queryset

join_to是要使用的实用程序功能.对于查询，您可以按以下方式使用它:

join_to is the utility function you want to use. For your query you can use it in as follows:

sq = Stats.objects.filter(date=my_date)
q = Site.objects.filter()
q = join_to(Site, sq, 'id', 'site_id', q, 'stats')

以下语句将打印与您的示例查询(带有子查询)类似的查询.

And following statement would print a query similar to you example query (with subquery).

print q.query

这篇关于Django ORM.联接子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！