Latest database entry of a kind

Using Django Using the ORM
1

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 :slight_smile: ) 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:

    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:


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. …

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
3

Hi there once again and again happy birthday :slight_smile:

Thx and thx once more for all your helpful tips. I will try this immediately :slight_smile: 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… :slight_smile:

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

4

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…

5

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.)

6

Tried it with this code:

    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

7

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.)

8

Thx :slight_smile: And I think we all have this special errors that we make again and again despite we know it better :stuck_out_tongue:

9

So, if I run the program with this:

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…

10

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”.

11

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 :stuck_out_tongue:

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

Tried it with:

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 :slight_smile:

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')
1 Like