Django: Get active threads by the time the last post is created

Question

Hallo people

I have two models, topics and posts in which one thread can have many posts. I want to get active threads sorting by 'post_set.createtime'. In the end, I want to get exactly ten threads that have the most recent activity. Is this possible using native SQL?

Thank you very much in advance.

[Copying model definitions from the OP answer to the body of the question.]

class Topic(models.Model):
    title = models.CharField(max_length=50)
    order = models.SmallIntegerField() #used for visual stuff

class Thread(models.Model):
    topic = models.ForeignKey(Topic)
    name = models.CharField(max_length=50)

class Post(Meta):
    thread = models.ForeignKey(Thread)
    text = models.TextField()

class Meta(models.Model):
    createuser = models.ForeignKey(User,default=None,blank=True,null=True,related_name="createuser")
    createtime = models.DateTimeField(default=datetime.datetime.now,blank=True,null=True)
    edituser = models.ForeignKey(User,default=None,null=True,related_name="edituser",blank=True)
    edittime = models.DateTimeField(default=None,null=True,blank=True)

Answer 1

Just add the "last_post_datetime" field to Thread and update this field in Post.save:

class Thread(models.Model)
    ...
    last_post_datetime = models.DateTimeField(blank=True,null=True) 

class Post(Meta):
    ...
    def save(self):
        super(Post, self).save()
        self.thread.last_post_datetime = max(self.thread.last_post_datetime, self.createtime)
        self.thread.save()

and use a simple query

Thread.objects.order_by('-createtime')[:10]

, , :

ALTER TABLE <post> ADD INDEX createtime (createtime);

Answer 2

, : DateTimeField Post "date_posted" - . :

Thread.objects.order_by('-post_set__date_posted')[:10]

Glader, , save(), auto_now_add True DateTimeField. .