How to select from multiple tables using Django ORM?

Question

Django

Good afternoon. I'm slowly learning django. Created the following model of employees, positions and relationships between them:

from django.utils import timezone
class Person(models.Model):
    last_name = models.CharField(max_length=50, help_text=u'Фамилия сотрудника', verbose_name=u'Фамилия')
    first_name = models.CharField(max_length=50, help_text=u'Имя сотрудника', verbose_name=u'Имя')
    patronymic = models.CharField(max_length=50, help_text=u'Отчество сотрудника', verbose_name=u'Отчество')

class Position(models.Model):
    position_name = models.CharField(max_length=200, help_text=u'Должность', verbose_name=u'Должность')

class Transfer(models.Model):
    """ В таблице храним даты переводов на новые должности """
    person = models.ForeignKey('structure_department.Person')
    new_position = models.ForeignKey('structure_department.Position')
    data_transfer = models.DateField(default=timezone.now, help_text=u'Дата перевода на новую должность', verbose_name=u'Дата')

Now I'm trying to create a list of employees with current positions in views for today:

def person_list1(request):
    persons = Transfer.objects.all().annotate(data=Max('data_transfer')).order_by('last_name')
    list_users = []
    for person in persons:
        max_data_transfer = Transfer.objects.filter(person=person).aggregate(max_data_transfer=Max('data_transfer'))['max_data_transfer']
        position = Transfer.objects.filter(person=person, data_transfer=max_data_transfer)[0].new_position
        list_users.append({'person': person, 'position': position})
    return render(request, 'structure_department/person_list.html', {'persons': persons})

It turned out difficult and ugly for a trivial task. Then I tried with a SQL query:

def person_list2(request):
    qwery = "SELECT t1.id, person.first_name, person.last_name, person.patronymic, pos.position_name, t1.data_transfer \
             FROM structure_department_transfer t1, \
                  structure_department_person person, \
                  structure_department_position pos \
             WHERE data_transfer = (SELECT MAX(data_transfer) \
                                    FROM structure_department_transfer t2 \
                                    WHERE t1.person_id = t2.person_id) \
             AND t1.person_id = person.id \
             AND t1.new_position_id = pos.id \
             GROUP BY person.last_name"
    persons = Transfer.objects.raw(qwery)
    return render(request, 'structure_department/person_list.html', {'persons': persons})

It turned out to be difficult again. Therefore, I would like to share my experience on the following issues:

1. How to correctly draw up a model of employees, positions and a table with information about who was promoted when?
   
2. How to correctly compose a query on Django ORM to display a list of employees with all current positions?
   

Answer 1

Person.objects.order_by(
  'id', '-transfer__data_transfer').distinct('id').values_list(
  'last_name', 'transfer__new_position__position_name', 'transfer__data_transfer')

Answer 2

1. According to the models, everything seems to be fine.
2. As I understand it, the main problem is to get the actual records in the transfers table;
It can be done in this way
Well, you can add two joins with the persons and positions tables yourself.