Subquery/Annotation of independent model keys

Using Django Using the ORM
1

Hi folks,

I´m still struggeling with complex queries in Django.

If have two independent models…Employees and Keys
Keysis a list of keys (surprisingly), looking like this:

class Keys(models.Model):

    key = models.CharField(max_length=25, unique=True)
    comment = models.CharField(max_length=100, null=True, blank=True)
    room = models.ManyToManyField(Rooms, through='LockingAuthorisations')

Employeesis looking like this (shortened by the non relevant fields):

class Employees(models.Model):

   ...
    last_name = models.CharField(max_length=100)
    first_name = models.CharField(max_length=100)
   ...
    key_number = models.CharField(max_length=15, unique=True, null=True, blank=True)
   ...

Both models do not have any relationship in database. But the key-number in Employees is an entry from Keys.

So, if I display a list of all keys, i want to display the last_nameand first_name of the employee that has received the key if it has been handed out.

So therefore I have to combine both models in a query. I have a vague imagination, about how but keep up doing it wrong.

The following code is not working but gives a “pseudo-code” idea of what i am looking for:

        emp_subquery = Employees.objects.filter(key_number=OuterRef('key'))
        key_qs = Keys.objects.all().annotate(F('emp_subquery'))
        context['key_list'] = key_qs.order_by('key')
2

It reads here like your key_number field here should be a foreign key to Keys, if that key_number is the value key in Keys.

3

Basically you are completely right. But i want the KeyAdministration and the Employee-Administration to be completely separated. So one could use the Employee-App without need of altering program-code if KeyAdministration is removed. The project is likely to run in differrent departments with different needs…