How to sort on number of visits in Django app?

Question

In Django (1.0.2), I have 2 models: Lesson and StatLesson.

class Lesson(models.Model):
    contents = models.TextField()
    def get_visits(self):
        return self.statlesson_set.all().count()

class StatLesson(models.Model):
    lesson = models.ForeignKey(Lesson)
    datetime = models.DateTimeField(default=datetime.datetime.now())

Each StatLesson registers 1 visit of a certain Lesson. I can use lesson.get_visits() to get the number of visits for that lesson.

How do I get a queryset of lessons, that's sorted by the number of visits? I'm looking for something like this: Lesson.objects.all().order_by('statlesson__count') (but this obviously doesn't work)

Answer 1

Django 1.1 will have aggregate support.

On Django 1.0.x you can count automatically with an extra field:

class Lesson(models.Model):
    contents = models.TextField()
    visit_count = models.IntegerField(default=0)

class StatLesson(models.Model):
    lesson = models.ForeignKey(Lesson)
    datetime = models.DateTimeField(default=datetime.datetime.now())

    def save(self, *args, **kwargs):
        if self.pk is None:
            self.lesson.visit_count += 1
            self.lesson.save()
        super(StatLesson, self).save(*args, **kwargs)

Then you can query like this:

Lesson.objects.all().order_by('visit_count')

Answer 2

You'll need to do some native SQL stuff using extra.

e.g. (very roughly)

Lesson.objects.extra(select={'visit_count': "SELECT COUNT(*) FROM statlesson WHERE statlesson.lesson_id=lesson.id"}).order_by('visit_count')

You'll need to make sure that SELECT COUNT(*) FROM statlesson WHERE statlesson.lesson_id=lesson.id has the write db table names for your project (as statlesson and lesson won't be right).