title: Optimizing Django queryset related comparisons
body: <p>I have a Django app where users upload photos, and leave comments under them. The data models to reflect these objects are <code>Photo</code> and <code>PhotoComment</code> respectively. </p>
  
  <p>There's a third data model called <code>PhotoThreadSubscription</code>. Whenever a user comments under a photo, the user is subscribed to that particular thread via creating an object in <code>PhotoThreadSubscription</code>. This way, he/she can be apprised of comments left in the same thread by other users subsequently. </p>
  
  <pre><code>class PhotoThreadSubscription(models.Model):
      viewer = models.ForeignKey(User)
      viewed_at = models.DateTimeField(db_index=True)
      which_photo = models.ForeignKey(Photo)
  </code></pre>
  
  <p>Every time a user comments under a photo, I update the <code>viewed_at</code> attribute of the user's <code>PhotoThreadSubscription</code> object for that particular photo. Any comments by <em>other users</em> that have a submission time of greater than <code>viewed_at</code> for that particular thread are therefore <strong>new</strong>.</p>
  
  <p>Suppose I have a queryset of comments, all belonging to unique photos that never repeat. I want to traverse through this queryset and find <strong>the latest unseen comment</strong>.</p>
  
  <p>Currently, I'm trying this in a very DB heavy way:</p>
  
  <pre><code>latest_unseen_comment = PhotoComment(id=1) #i.e. a very old comment
  for comment in comments:
      if comment.submitted_on &gt; PhotoThreadSubscription.objects.get(viewer=user, which_photo_id=comment.which_photo_id).viewed_at and comment.submitted_on &gt; latest_unseen_comment.submitted_on:
          latest_unseen_comment = comment
  </code></pre>
  
  <p>This is obviously not a good way to do it. For one, I don't want to do DB calls in a for loop. How do I manage the above in one call? Specifically, how do I get the relevant <code>PhotoThreadSubscription</code> queryset in one call, and next, how do I use that to calculate the <strong>max_unseen_comment</strong>? I'm highly confused right now. </p>
  
  <hr>
  
  <pre><code>class Photo(models.Model):
      owner = models.ForeignKey(User)
      image_file = models.ImageField(upload_to=upload_photo_to_location, storage=OverwriteStorage())
      upload_time = models.DateTimeField(auto_now_add=True, db_index=True)
      latest_comment = models.ForeignKey(blank=True, null=True, on_delete=models.CASCADE)
  
  
  class PhotoComment(models.Model):
      which_photo = models.ForeignKey(Photo)
      text = models.TextField(validators=[MaxLengthValidator(250)])
      submitted_by = models.ForeignKey(User)
      submitted_on = models.DateTimeField(auto_now_add=True)
  </code></pre>
  
  <hr>
  
  <p>Please ask for clarification if the question seemed hazy.</p>