[FIXED] How to filter on a foreign key that is grouped?

Issue

Model:

from django.db import models

class Person(models.Model):
    name = models.CharField(max_length=100)


class Result(models.Model):
    person = models.ForeignKey(Person, on_delete=models.CASCADE)
    outcome = models.IntegerField()
    time = models.DateTimeField()

Sql:

select * from person as p
inner join (
   select person_id, max(time) as max_time, outcome from result
   group by person_id
) r on p.id = r.person_id
where r.result in (2, 3)

I'm wanting to get the all person records where the last result outcome was either a 2 or 3. I added the raw sql above to further explain.

I looked at using a subquery to filter person records that have a matching result id

sub_query = Result.objects.values("person_id").annotate(max_time=Max("time"))

however using values strips out the other fields.

Ideally I'd be able to do this in one person queryset but I don't think that is the case.

Solution

As a person may have multiple result records and I only want to check the last record, A subquery was the only way I could find to do this

last_result = Subquery(
    Result.objects.filter(person_id=OuterRef("pk")).order_by("-time").values("result")[:1]
)

people = Person.objects.all().annotate(max_time=Max("result__time"), current_result=last_result).filter(current_result__in=[2,3)

First I create a sub query that will return the last result record. Then I add this as a field in the people query so that I can filter on that for only results with 2 or 3.

This was it will only return person records where the current result is a 2 or 3.

Answered By - Ozzy