How to select from multiple tables in one query using 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 each Employee in the table next to the Company. It is quite simple by calling employees = Employee.objects.all() and in the template loop through it and calling {{employee.company.name}} .

The problem with these solutions is that a new query will be created for each element of the loop. Therefore, for each Employee there will be one request to the 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 want to make this connection initially in the same request, getting Workers. Something like that:

 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 with to solve this (without raw sql)? Or should this behavior be ignored, cached, and considered "optimized"?

Answer 1

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

 Employee.objects.select_related() 

Answer 2

I think what you are looking for is the select_related method of your query set. See document

select_related ()

Returns a QuerySet that will automatically “monitor” the foreign key of the relation, choosing what additional data the associated object will when it executes its query. This is a performance improvement that leads to (sometimes much) large queries, but means continued use of a foreign key relationship does not require database queries

Answer 3

This is an old question, let me give a new answer.

Actually, you can do this:

 employees = Employee.objects.all().values('id','name','company__name') 

then Django will automatically search for the company class and find the company name for you.

use the {{employee.company__name}} on the template page, then it will display the company name correctly.

Answer 4

With raw requests

  qry1 = "SELECT c.car_name, p.p_amount FROM pay p, cars c where p.user_id=%s;" cars = Cars.objects.raw(qry1, [user_id])