How to join 3 tables in query with Django

Question

models.py

class Employee(models.Model):
    emp_no = models.IntegerField(primary_key=True)
    first_name = ...
    last_name = ...

    # emp_no first_name  last_name
      ------ ----------  ----------
      10005  Christian   Erde

class DeptEmp(models.Model):
    emp_no = models.ForeignKey(Employee, on_delete=models.CASCADE)
    dept_no = models.ForeignKey(Department, on_delete=models.CASCADE)

    # dept_no_id  emp_no_id 
      ----------  ----------
      d003        10005     

class Department(models.Model):
    dept_no = models.CharField(primary_key=True, max_length=4)
    dept_name = models.CharField(unique=True, max_length=40)

    #  dept_no     dept_name      
       ----------  ---------------
       d003        Human Resources

views.py

class EmpList(ListView):
    queryset = Employee.objects.all().select_related('deptemp').select_related('department')

employee_list.html

{% for emp in object_list %}
    {{ emp.first_name }}
    {{ emp.last_name }}
    {{ emp.department.dept_name }}
{% endfor %}

I got this error message on the browser: Invalid field name(s) given in select_related: 'deptemp', 'department'. Choices are: (none)

I think you're looking for a ManyToManyField (https://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.ManyToManyField). This creates an intermediary table on the db similar to your DeptEmp model. — munsu, May 04 '17 at 01:50
@karthikr, thanks for pointing that out. I corrected it. Please see my edited template file. — Jason TK, May 04 '17 at 02:08

score 13 · Accepted Answer · edited Nov 29 '22 at 19:48

select_related() returns a QuerySet that will follow foreign-key relationships.

from django.db import models
class City(models.Model):
    # ...
    pass
class Person(models.Model):
    # ...
    hometown = models.ForeignKey(
        City,
        on_delete=models.SET_NULL,
        blank=True,
    null=True,
    )

class Book(models.Model):
    # ...
    author = models.ForeignKey(Person, on_delete=models.CASCADE)

Then a call to Book.objects.select_related('author__hometown')

For your examples:

class EmpList(ListView):
    queryset = DeptEmp.objects.all().select_related('emp_no').select_related('dept_no')

employee_list.html

    {% for deptemp in object_list %}
        {{ deptemp.emp_no.first_name }}
        {{ deptemp.emp_no.last_name }}
        {{ deptemp.dept_no.dept_name }}
    {% endfor %}

ah. thank you so much. that is awesome. I got the table names instead of the keys in select_related(). — Jason TK, May 04 '17 at 07:22

How to join 3 tables in query with Django

1 Answers1

Linked