Latest database entry of a kind

Using Django Using the ORM
2

I’m guessing your models look something like this:

class Employee(Model):
    last_name = CharField(...)
    (other fields as appropriate)
    ...

class Employment(Model):
    employee = ForeignKey(Employee, ...)
    duty_end = DateField()
    (other fields as appropriate)
    ...

and what I’m understanding of your question is that you want a set of Employee where their most recent Employment matches some criteria.

If I’m understanding you correctly, there are a couple ways you can do this. The way that we do it would be to annotate the Employee with the id of the current Employment. You can then use that value in subsequent query clauses.

For example:

Employee.objects.annotate(
  current = Subquery(
    Employment.objects.filter(
      employee=OuterRef('id')
    ).order_by('-duty_end').values('id')[:1]
  )
).filter(employment=current, ...). ...

If you’re only looking to then determine if the end_date is before today, you could include that test as a Conditional Expression within that subquery to annotate the queryset with the retirement status and return that retirement status to the outer query instead of the id.

1 Like