title: How to query Django ORM on models connected with latest record
body: <p>Hi I have a Django Project with the following models</p>
  <pre class="lang-py prettyprint-override"><code>class Organization(models.Model):
      name = models.CharField(&quot;Name&quot;, max_length=128, unique=True)
      description = models.CharField(&quot;Description&quot;, max_length=256)
  
  class Scholar(models.Model):
      organization = models.ForeignKey(Organization, on_delete=models.CASCADE)
      name = models.CharField(&quot;Name&quot;, max_length=128)
      title = models.CharField(&quot;Title&quot;, max_length=256)
      
      def get_latest_snapshot(self):
          return self.snapshotscholar_set.latest('date_crawled')
  
  class SnapshotScholar(models.Model):
      scholar = models.ForeignKey(Scholar, on_delete=models.CASCADE)
      date_crawled = models.DateTimeField(&quot;Date Crawled&quot;, auto_now_add=True, db_index=True)
      title = models.CharField(&quot;Title&quot;, max_length=256)
  
  class SnapshotScholarPublication(models.Model):
      snapshot_scholar = models.ForeignKey(SnapshotScholar, on_delete=models.CASCADE)
      title = models.CharField(&quot;Title&quot;, max_length=256)
      citation_count = models.IntegerField()
  </code></pre>
  <p>Now when I want to get a count of the number of scholars per organization I can do <code>Organization.objects.annotate(num_scholars=Count('scholar'))</code>. But how do I get the number of publication per organization when I only want to count publications of the latest snapshot. That is, I want it sorted by SnapshotScholar.date_crawled, and I want all the publications on the latest SnapshotScholar in the database.</p>
  <p>Going by some of the questions on here I managed to create this SQL -</p>
  <pre><code>SELECT COUNT(pub.id) as publications, org.id
  FROM (main_snapshotscholarpublication pub, main_snapshotscholar snap, main_scholar scholar, main_organization org)
  INNER JOIN (
      SELECT MAX(main_snapshotscholar.date_crawled) as latest_date, main_snapshotscholar.scholar_id as 'id'
      FROM main_snapshotscholar
      GROUP BY main_snapshotscholar.scholar_id
  ) as latest_snap ON (latest_snap.id = snap.id)
  WHERE pub.snapshot_scholar_id = snap.id
  AND snap.scholar_id = scholar.id
  AND scholar.organization_id = org.id
  GROUP BY org.id
  </code></pre>
  <p>The results I'm getting with this raw SQL is an error margin of 1-5% from the actual count numbers. Can someone please help me figure out how to get the right results?</p>
  <p>Thanks</p>
  <p>EDIT: As per request of one of the people answering the question here is some sample data - <a href="https://pastebin.com/4ZJkymeb" rel="nofollow noreferrer">https://pastebin.com/4ZJkymeb</a> . Just load the data with <code>python manage.py loaddata data.json</code></p>