How do I select from multiple tables in one query with Django?


Question

I have two tables, one "Company" and one "Employee":

class Company(models.Model):
    name = models.CharField(max_length=60)

class Employee(models.Model):
    name = models.CharField(max_length=60)
    company = models.ForeignField(Company)

And I want to list every Employee in a table, with the Company next to it. Which is simple enough by calling employees = Employee.objects.all() and in the template loop trough it and calling {{employee.company.name}}.

The problem with this solutions is that it will be created a new query for each item in the loop. So for each Employee there will be one query to Company looking something like this:

SELECT `company`.`id`, `company`.`name`
FROM `company`
WHERE `company`.`id` = 1 # This will of course be the employee.company_id

Instead I wish to make this join initially in the same query getting the Employees. Something like this:

SELECT `employee`.`name` AS `name`,
       `company`.`name` AS `company_name`
FROM `employee` INNER JOIN `company` ON `employee`.`company_id` = `company`.`id`

Is this possible with the Django QuerySet? If not, is there a way I can work around to solve this(without raw sql)? Or should this behavior be ignored, cached and considered "optimized"?

1
10
3/11/2010 2:17:04 PM

Accepted Answer

Using select_related() will pre-populate the appropriate attributes:

Employee.objects.select_related()
22
11/13/2018 4:12:27 PM

I guess what you're looking for is the select_related method of your queryset. See the doc

select_related()

Returns a QuerySet that will automatically "follow" foreign-key relationships, selecting that additional related-object data when it executes its query. This is a performance booster which results in (sometimes much) larger queries but means later use of foreign-key relationships won't require database queries


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon