Select instance/row with max field/column value per with field/column (group by)

Question

So, I have these models:

class Computer(models.Model):
    hostname = models.CharField(primary_key=True, max_length=6)
    <other computer info fields>

class ComputerRecord(models.Model):
    id = models.AutoField(primary_key=True)
    pc = models.ForeignKey(Computer, on_delete=models.CASCADE)
    ts = models.DateTimeField(blank=False)
    <other computerrecord info fields>

I want to get the row / computerrecord instance that has the max ts for each pc (Computer model)

In sql would be something like this:

SELECT hub_computerrecord.*
FROM hub_computerrecord
JOIN (
    SELECT pc_id, MAX(ts) AS max_ts
    FROM hub_computerrecord
    GROUP BY pc_id
) AS maxs ON hub_computerrecord.pc_id = maxs.pc_id
WHERE hub_computerrecord.ts = maxs.max_ts;

Note (edit): There are lot of ComputerRecord instances (10000+) so anything too inefficient won't work

Answer 1

Try this

from django.db.models import Max, F

qs = ComputerRecord.objects.annotate(
    max=Max('pc__computerrecord__ts')
).filter(
    ts=F('max')
)

Yes, this form expression doesn't build the exact SQL query in the OP, but it produces the same result (there may be some performance issues, not sure about the matrics)

Alternatively, you can execute the raw SQL using the raw() method as

raw_query = """
SELECT hub_computerrecord.*
FROM hub_computerrecord
JOIN (
    SELECT pc_id, MAX(ts) AS max_ts
    FROM hub_computerrecord
    GROUP BY pc_id
) AS maxs ON hub_computerrecord.pc_id = maxs.pc_id
WHERE hub_computerrecord.ts = maxs.max_ts;
"""

qs = ComputerRecord.objects.raw(raw_query)

Answer 2

Add a boolean field in ComputerRecord model, for e.g. max_status. Identify the record with max 'ts' value for Computer object and set it to True. so if you have 10000 records for a particular computer model one record will have True and all the rest 9999 records will have False in max_status field. This needs to be done only once for each computer model.

Next time when you are adding a new row in ComputerRecord model just compare 'ts' of the of new record to 'ts' of the existing record with max_status value as True. if new record has higher 'ts' value set max_status as True for this record and change max_status of previous record to False else no change required.

When you need to find out the instance of ComputerRecord with max 'ts' , query only on max_status=True