Django ORM. Joining subquery

In Django v2.0 use FilteredRelation

Site.objects.annotate(    t=FilteredRelation(        'stats', condition=Q(stats__date='my-date')).filter(t__google_pr__in=[...])

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

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 ForeignObjectfrom django.db.models.options import Optionsfrom django.db.models.sql.where import ExtraWherefrom django.db.models.sql.datastructures import Joinclass 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), paramsdef 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 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

Look at it this way: you want to see statistics with accompanying site data for certain date, which translates to:

Stats.objects.filter(date=my_date).select_related('site')