How to call double nested reverse foreign key relation in django?

Question

I'm working on forum project. Currently, I need to fetch somehow the latest posts for each category.

My models structure is following (pseudo code):

class Category(models.Model):
   ...

class Topic(models.Model):
   category = models.ForeignKey(Category, on_delete=models.CASCADE)
   ...

class Post(models.Model):
   topic = models.ForeignKey(Topic, on_delete=models.CASCADE)
   created = models.DateTimeField(auto_now_add=True)
   ...

I need to fetch latest Post objects within Category model's functions, so I can call it latter on in my template.

With single reverse foreign key call I would do something like: category: Category = self.topic_set.all() but I need to get one level deeper for Post.

Is there any smarter way to do it instead of list comprehension like [topic.post_set.first() for topic in self.topic_set.all()] ??

Answer 1

You can do this in two queries with a Subquery expression ^[Django-doc] that will obtain the latest Post per Topic:

from django.db.models import OuterRef, Subquery

post_ids = Topic.objects.filter(
    category=my_category
).values_list(
    Subquery(
        Post.objects.filter(
            topic_id=OuterRef('pk')
        ).order_by('-created').values('pk')[:1]
    ),
    flat=True
)

posts = Post.objects.filter(pk__in=post_ids)