3

Let's assume I've the classic blog Post with multiple related Comments model. I'm trying to get a list of posts each annotated with its last comment (or at least a single field from it).

The closest I've gotten is via:

Post.objects.annotate(last_comment=F('comment__text'))

But this just returns the body for the first commend, and I explicitly want the last.

Am I missing something pretty obvious? Or is there some smart way of doing this that I'm failing to see?

WhyNotHugo
  • 9,423
  • 6
  • 62
  • 70
  • There are many ways of doing this. Check this related question and decide which works best for you: http://stackoverflow.com/questions/31234880/annotate-with-value-of-latest-related-in-django-1-8-using-conditional-annotation/31313003#comment50615846_31313003 – Mark Galloway Aug 25 '15 at 01:03
  • Never tried this - but what happens if you specify the `ordering` meta attribute on the comment model ? – karthikr Aug 25 '15 at 01:23
  • @MarkGalloway Regrettably, the best solution mentioned there there is keeping a redundant attribute to store the last `Comment`. That's what I'm doing now and would like to avoid. – WhyNotHugo Aug 25 '15 at 02:20
  • @karthikr That *seems* to work, though it sounds a bit like an instance of "programming by coincidence". – WhyNotHugo Aug 25 '15 at 02:23
  • Haha.. No, it is not a coincidence. The only downside is, this ordering is the default everywhere, except for explicit order_by – karthikr Aug 25 '15 at 02:25
  • @karthikr That won't really be an issue since I *always* want this on my scenario (I'm actually overriding the default queryset with this annotation). Is there any clear explanation to why the first instance is used though? Also, you might want to make your comment into an answer. :) – WhyNotHugo Aug 25 '15 at 02:36

1 Answers1

1

If you want to retrieve the latest comment, you might want to specify an ordering on the Meta

Something like this:

class Comment(models.Model):
   ...
   class Meta:
       ordering = ('-created_at', ) #or id, or whatever

Then, your query

Post.objects.annotate(last_comment=F('comment__text'))

would retrieve the latest post.

Specifying the ordering (in this case) is the equivalent of saying Comments.objects.order_by('-created_at'), and it would always work, as you want the latest comment.

One caveat here though is, the ordering is applied by default everywhere the comments are retrieved. Also, has some database implications as the ordering is not free

karthikr
  • 97,368
  • 26
  • 197
  • 188
  • Actually, I've now realized that `get()` fails with this, because I seem to be getting N `Post`s, where N is the amount of `Comment`s for that post. – WhyNotHugo Aug 25 '15 at 03:58
  • Looks like this *does* work as expected in all scenarios: `Post.objects.distinct('id').annotate(last_comment=F('comment__text'))` – WhyNotHugo Aug 25 '15 at 04:00
  • Scrub that last comment, that makes `get` work, but makes `filter` fail with an SQL error. – WhyNotHugo Aug 25 '15 at 04:02