CODEWITHSUNDEEP
django
Aliquis
Aliquis

Reputation: 2309

Django: Query with foreign key

I have two models called Thread and Post. A Thread has 0..* posts.

Now, I need a query that gets all threads sorted by the datetime of the latest post in the thread. In case there is no post yet in the thread, the datetime of the thread creation is important.

Honestly, I am a bit overwhelmed with the database query.

Thread:
created_at = DateTimeField()

Post
thread = ForeignKey(Thread)

My current approach does not work:

newest = Post.objects.filter(thread=OuterRef('pk')).order_by('-created_at')
threads = Thread.objects.annotate(
    latest_post=Case(
        When(Exists(Subquery(newest.values_list('created_at')[:1])),
             then=Value(Subquery(
                newest.values_list('created_at')[:1]),
            ),
            default=Value(Thread.created_at)))).order_by('-latest_post')

Can someone help me?

Upvotes: 3

Views: 283

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

You can annotate these with the Max aggregate [Django-doc], and use the Coalesce function [Django-doc] as a fallback mechanism, like:

from django.db.models import Max
from django.db.models.functions import Coalesce

Thread.objects.annotate(
    latest_post=Coalesce(Max('post__created_at'), 'created_at')
).order_by('-latest_post')

The latest_post is thus the timestamp of the maximum created_at of the related Post objects. In case there are no related Post objects, we fallback on the created_at field of the Thread.

Upvotes: 5

Related Questions