How do I select from multiple tables in one query

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"?

Answer 1

With raw queries

    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])

Answer 2

It is an old question, let me provide a new answer.

Actually, you can do this:

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

then, Django will automatically lookup Company class and find the company name for you.

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

Answer 3

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

Employee.objects.select_related()

Answer 4

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