# Latest database entry of a kind

**URL:** <https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330>\
**Category:** Using the ORM\
**Created:** [July 16, 2023, 8:20am UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330 "2023-07-16T08:20:27Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 16, 2023, 8:20am UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/1 "2023-07-16T08:20:27Z")

</div>

Hi there once again,

although I have solved more problems in my project than I have left (also because of the helpful brains of all of you in here 🙂 ) there are some left:

Let’s say we have a database of employees, and a On-To-Many relationship to their employments.  
Adittionally we have some laws concerning data-security, that make it neccessary to store a Boolean `keep_data` field, depending on the employee having decided to want his data deleted after Employment or not.

Additionally, after an Employee has retired, only people with the right user-role still can see their data.

And finally: an Employee can have more than one employment over the time. Not simultaneously of course but one after another on a timeline.

So if i try to handle this in displaying datasets by appropriate filtering, i have the folowing for the use case, that i want get displayed a “retired employee” Message in the database view of this special employee:

```auto
    if Employments.objects.filter(employee_id=employee).exists():
        context['no_employment'] = 'False'
        current_employment = Employments.objects.filter(employee_id=employee).latest('duty_begin')
        filters &= Q(duty_end__lt=datetime.date.today()) & Q(duty_begin=current_employment.duty_begin)
        filters &= Q(keep_data=1)

    if Employments.objects.filter(filters).exists():
        context['info'] = 'retired employee'

    return context

```

This works without any problems. But how to deal with the use case, that i want have all recorded employments generelly filtered for retired employees?

I tried it with:

```auto

email_queryset = Employees.objects.all().order_by('last_name').values().distinct()
......
if 'retired' in request.POST:
    retired = request.POST['retired']
    context['retired'] = retired
    if retired == "only retired":
        filters &= Q(employments __duty_end__ lt=datetime.date.today()) & Q(employments__keep_data=1)
    elif retired == "with retired":
        filters &= ~Q(Q(employments __duty_end__ lt=datetime.date.today()) & Q(employments__keep_data=0))

context['emails'] = email_queryset.filter(filters)

```

Which basically works, but fails in all cases, where an employee has more than one employment, because only the last one is active and should be chosen for the query, but of course, with this filters, all entries are looked up and so he will be dealt like retired if one of the former employments match the query. So I am looking for a way to get the latest entry of n employments per employee but i don`t know how if i have no specific id for a single dataset. …

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 16, 2023, 11:42am UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/2 "2023-07-16T11:42:43Z")

</div>

I’m guessing your models look something like this:

```auto
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:

```auto
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](https://docs.djangoproject.com/en/4.1/ref/models/conditional-expressions/#conditional-expressions) within that subquery to annotate the queryset with the retirement status and return that retirement status to the outer query instead of the id.

---

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 16, 2023, 3:04pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/3 "2023-07-16T15:04:21Z")

</div>

Hi there once again and again happy birthday 🙂

Thx and thx once more for all your helpful tips. I will try this immediately 🙂 And checking wether end\_date is before today is not necessary, because in case of temporary employments, the end date could be entered way before retirement… 🙂

Your assupmtions about the relevant parts of my models are correct! 🙂

---

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 16, 2023, 3:21pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/4 "2023-07-16T15:21:35Z")

</div>

Hi again,

I importet `Subquery` and `OuterRef` to the functions.py where the code is located…but in the last line, the “`current`” in `.filter(employment=current,`is still marked als unresolved reference…

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 16, 2023, 3:39pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/5 "2023-07-16T15:39:12Z")

</div>

Can you post the complete query? (And what do you mean by “marked” as an undefined reference? If it’s your ide showing that, ignore it.)

---

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 17, 2023, 7:35pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/6 "2023-07-17T19:35:21Z")

</div>

Tried it with this code:

```auto
    list_queryset = Employees.objects.annotate(
                        current=Subquery(
                            Employments.objects.filter(
                                employee=OuterRef('id')
                            ).order_by('-duty_end').values('id')[:1]
                        )
                    ).filter(employments=current).order_by('last_name')

```

and `current` in the last line is underlined red by the IDE, that’s correct. If I ignore it, Django throws a NameError :  
`name "current" is not defined`

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 17, 2023, 7:57pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/7 "2023-07-17T19:57:26Z")

</div>

Sorry, silly (sloppy) me. It needs to be `filter(employments=F('current'))` since it’s a reference to a field. (Ain’t no way I’m going to admit to how many times I’ve made that mistake.)

---

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 17, 2023, 8:08pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/8 "2023-07-17T20:08:27Z")

</div>

Thx 🙂 And I think we all have this special errors that we make again and again despite we know it better 😛

---

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 17, 2023, 8:47pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/9 "2023-07-17T20:47:52Z")

</div>

So, if I run the program with this:

```auto
list_queryset = Employees.objects.annotate(
                        current=Subquery(
                            Employments.objects.filter(
                                employee=OuterRef('id')
                            ).order_by('-duty_end').values('id')[:1]
                        )
                    ).filter(employments=F('current')).order_by('last_name')

```

the good news is, that nothing bad has happened, it is just working like before. That means, that Employees with more than one Employment in database keep showing up in query, but i only want to see retired personnel…

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 17, 2023, 8:56pm UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/10 "2023-07-17T20:56:08Z")

</div>

Correct, you need to add whatever filtering may be necessary to further restrict the selections (hence the ellipses both in and out of the `filter` clause to indicate more can/should be added)

It wasn’t (isn’t) clear to me precisely what defines “retired personnel”.

---

<div class="post-metadata">

**Author:** ![doc1977](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doc1977/32/13416_2.png) [@doc1977](https://forum.djangoproject.com/u/doc1977)\
**Post date:** [July 18, 2023, 5:54am UTC](https://forum.djangoproject.com/t/latest-database-entry-of-a-kind/22330/11 "2023-07-18T05:54:37Z")

</div>

Yeah, of course I was aware of, that your code only preselects one last Employment per Employee and further filtering is needed. I DID it, but made an error that I seem to likely repeat 😛

The old filter(A,B) is NOT filter(A).filter(B) thing.

Tried it with:

```auto
list_queryset = Employees.objects.annotate(
                        current=Subquery(
                            Employments.objects.filter(
                                employee=OuterRef('id')
                            ).order_by('-duty_end').values('id')[:1]
                        )
                    ).filter(employments=F('current'))
    filters = Q()  
    context = {}

...
     if retired == "retired":
          filters &= Q(employments __duty_end__ lt=today) & Q(employments__keep_data=1)

    context['results'] = list_queryset.filter(filters).order_by('last_name')

```

Of course, this leads to a .filter(A).filter(B) behaviour…so I changed it and now it works 🙂

```auto
list_queryset = Employees.objects.annotate(
                        current=Subquery(
                            Employments.objects.filter(
                                employee=OuterRef('id')
                            ).order_by('-duty_end').values('id')[:1]
                        )
                    )

...
   if retired == "retired":
         filters &= Q(employments __duty_end__ lt=today) & Q(employments__keep_data=1)
...

    context['results'] = list_queryset.filter(filters, employments=F('current')).order_by('last_name')

```