Django ORM. Joining subquery

Question

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.

If I use:

Site.objects.filter(stats__date=my_date)

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'

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

How can I translate this query to Django ORM?

Thanks.

Answer 1

In Django v2.0 use FilteredRelation

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