title: How do I write a Django query with a subquery as part of the WHERE clause?
body: <p>I'm using Django and Python 3.7.  I'm having trouble figuring out how to write a Django query where there's a subquery as part of a where clause.  Here's the models ...</p>
  
  <pre class="lang-python prettyprint-override"><code>class Article(models.Model):
      objects = ArticleManager()
      title = models.TextField(default='', null=False)
      created_on = models.DateTimeField(auto_now_add=True)
  
  
  class ArticleStat(models.Model):
      objects = ArticleStatManager()
      article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='articlestats')
      elapsed_time_in_seconds = models.IntegerField(default=0, null=False)
      votes = models.FloatField(default=0, null=False)
  
  
  class StatByHour(models.Model):
      index = models.FloatField(default=0)
      # this tracks the hour when the article came out
      hour_of_day = IntegerField(
          null=False,
          validators=[
              MaxValueValidator(23),
              MinValueValidator(0)
          ]
      )
  </code></pre>
  
  <p>In PostGres, the query would look similar to </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT *
  FROM article a,
       articlestat ast
  WHERE a.id = ast.article_id
    AND ast.votes &gt; 100 * (
      SELECT "index" 
      FROM statbyhour 
      WHERE hour_of_day = extract(hour from (a.created_on + 1000 * interval '1 second')))
  </code></pre>
  
  <p>Notice the subquery as part of the WHERE clause</p>
  
  <pre class="lang-sql prettyprint-override"><code>ast.votes &gt; 100 * (select index from statbyhour where hour_of_day = extract(hour from (a.created_on + 1000 * interval '1 second'))) 
  </code></pre>
  
  <p>So I thought I could do something like this ...</p>
  
  <pre><code>hour_filter = Func(
      Func(
          (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
          function='HOUR FROM'),
      function='EXTRACT')
  ...
  votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
      "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                  StatByHour.objects.get(hour_of_day=hour_filter) * day_of_week_index)
  qset = ArticleStat.objects.filter(votes_criterion1 &amp; votes_criterion2,
                                    comments__lte=25)
  </code></pre>
  
  <p>but this results in a "Cannot resolve keyword 'article' into field. Choices are: hour_of_day, id, index, num_articles, total_score" error.  I think this is because Django is evaulating my "StatByHour.objects" query before the larger query within it is run, but I don't know how to rewrite things to get the subquery to run at the same time.</p>
  
  <p><b>Edit:</b> K, moved my subquery into an actual "Subquery" function and referenced the filter I created using OuterRef ...</p>
  
  <pre><code>hour_filter = Func(
      Func(
          (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
          function='HOUR FROM'),
      function='EXTRACT')
  query = StatByHour.objects.get(hour_of_day=OuterRef(hour_filter))
  
  
  ...
  votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
      "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                  Subquery(query) * 
                   day_of_week_index)
  qset = ArticleStat.objects.filter(votes_criterion1 &amp; votes_criterion2,
                                    comments__lte=25)
  </code></pre>
  
  <p>and this results in an </p>
  
  <pre><code>This queryset contains a reference to an outer query and may only be used in a subquery.
  </code></pre>
  
  <p>which is odd because I am using it in a subquery.</p>
  
  <p><b>Edit #2:</b> Even after changing the query per the answer given ...</p>
  
  <pre><code>hour_filter = Func(
      Func(
          (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
          function='HOUR FROM'),
      function='EXTRACT')
  query = StatByHour.objects.filter(hour_of_day=OuterRef(hour_filter))[:1]
  
  ...
  votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
      "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                  Subquery(query) *
                                  day_of_week_index)
  qset = ArticleStat.objects.filter(et_criterion1 &amp; et_criterion2 &amp; et_criterion3,
                                    votes_criterion1 &amp; votes_criterion2,
                                    article__front_page_first_appeared_date__isnull=True,
                                    comments__lte=25)
  </code></pre>
  
  <p>I still get the error</p>
  
  <pre><code>'Func' object has no attribute 'split'
  </code></pre>