[Fixed]-Django annotating with a first element of a related queryset


You can use a Subquery to annotate on a single field from the most recent related object:

comments = Comment.objects.filter(

You could annotate on several fields this way, but that would hurt performance (each correlated subquery runs separately, and for each row, which is better than N+1 queries, but worse than a single join).

You can build up a JSON object on a single field, and then annotate that on:

comments = Comment.objects.filter(
        models.Value('author'), models.F('author'),
        models.Value('timestamp'), models.F('timestamp'),

(It’s even possible to get the whole object as JSON, and then re-inflate that in Django, but that’s a bit hacky).

Another solution could be to fetch the most recent comments seperately, and then combine them with the posts:

comments = Comment.objects.filter(
).distinct('post').order_by('post', '-timestamp')
posts = Post.objects.filter(...).order_by('pk')

for post, comment in zip(posts, comments):

You would need to make sure the posts and comments are in the same order here: these queries are. This would also fail if there was not a comment on each post.

A workaround for that could be to put the comments into a dict keyed by post id, and then fetch the matching one for each post.

comments = {
    comment.post_id: comment
    for comment in Comment.objects.distinct('post').order_by('post', '-timestamp')
for post in Post.objects.filter(...):
    top_comment = comments.get(post.pk)
    # whatever


Part of what you’re looking for is select_related. You’ll also need to use annotate like you expected.

# I assume you have thread_id given to you.
last_reply = Post.objects.annotate(
    thread_images=Count('thread__post_set__image__id', distinct=True),
    replies=Count('thread__post_set__id', distinct=True),

Leave a comment