How to order parent table field by child table updates-CodePudding

i hope i wrote the title correctly. i just started django recently, and i have problem with queryset.

so i have these models, views and template file.

models.py

class Topic(models.Model):
name = models.CharField(max_length=200)

def __str__(self):
    return self.name


class Room(models.Model):
host = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
topic = models.ForeignKey(Topic, on_delete=models.SET_NULL, null=True)
name = models.CharField(max_length=200)
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(auto_now_add=True)

class Meta:
    ordering = ['-updated', '-created']

def __str__(self):
    return self.name

views.py

def topics(request):
"""topics page for listed view of topics"""

room_count = Room.objects.all().count()
topics = Topic.objects.all()

context = {'topics': topics, 'room_count': room_count}
return render(request, 'base/topics.html', context)

topics.html

        {% for topic in topics %}
        <li>
          <a href="{% url 'home' %}?q={{topic.name}}">{{ topic.name }}<span> 
        {{topic.room_set.all.count}}</span></a>
        </li>
        {% endfor %}

this loop clearly sorts topics by its created time. but when there is a new room or if there is an update in any existing room, i want the topic related to that room to be at the top. how can I do this? I tried several attempts, but it doesn't solve the issue. Or should I change my database architecture? since I'm fairly new to databases, I'm having a headache to solve this issue. I've been googling for two or three days but I couldn't find a solution.

thanks in advance.

CodePudding user response：

Model.objects.all().order_by("-id")

from latest to oldest

CodePudding user response：

you can achieve this by annotating a new colum and sort by it

from django.db.models import OuterRef, Subquery

#subquery which gets latest update from all rooms for the given topic
latest_room_update = Room.objects.filter(topic=OuterRef('pk')).order_by('-updated')

topics = Topics.objects.all()
#add the new column
topics = topics.annotate(latest_related_room_update=Subquery(latest_room_update.values['updated'][:1))

#sort
topics = topics.order_by('-latest_related_room_update')

edit because .latest() in subquery seems to cause query execution and therefore result in failure