Django query for latest version of a object

Question

I am trying to write a query in Django but I'm not sure whats the best way to write this. Also assuming that the database is mySQL. This matters if distinct is used.

  class Homework(models.Model):
    title = TextField()


  class Assignment(models.Model):
      homework_id = Foreignkey(Homework, on_delete=models.CASCADE)
      task = IntegerField(default=1)
      version = IntegerField(default=1)

Given the models above I want to get all the assignments in a particular homework where the assignments are the latest version of the task. example:

Homework_id: 1
    assignment v1, t1
    assignment v2, t1
    assignment v1, t2

If I have one homework object where the assignment with task 1 and assignment task 2 are in it then the result query should return Assignment t1, v2 and Assignment t2, v1

v refers to version
t refers to task

Answer 1

You could try annotating the tasks with the max version number like this:

Assignment.objects.filter(homework_id=1).values('task').annotate(version=Max('version'))

Which would get you something like:

<QuerySet [{'task': 1, 'version': 2}, {'task': 2, 'version': 1}]>

Answer 2

I know some people might turn up their nose about what I am proposing, but after trying a lot of complicate and resource intensive approaches I came at this simple one that I will explain.

A big issue comes from the fact that you cannot query all database backends using distinct(field_name) E.G.:

MyModel.objects.all().order_by('id', '-version').distinct('id')

So what I have done is simply adding a BooleanField called latest that will be set to True when the version is the latest and False otherwise.

Some considerations:

• When you add a record you should use a transaction and set the field to True to the record you are adding and False to all the others
• When you make a query you just filter using .filter(latest=True) and everything is a lot more easy and with good performances.

In the example code the django Model is subclassed to perform operations on save.

from django.db import transaction

class MModel(models.Model):
    @transaction.atomic
    def save(self, *args, **kwargs):
        cls = type(inst)  # getting model

        # This will reset the boolean field "latest" for all records
        # with same used "myid"
        qry_same_id = cls.objects.filter(myid=inst.myid)
        for record in qry_same_id:
            record.latest = False
            record.save()

        # at this point I set the record I am saving to be the latest
        self.latest = True

        super().save(*args, **kwargs)

class MyModel(MModel):
    # Example model
    myid = models.CharField(max_length=20)
    version = models.IntegerField()
    latest = models.BooleanField()