Django Many-to-Many Relationship

Introduction to the Django Many-to-Many relationship

In a many-to-many relationship, multiple rows in a table are associated with multiple rows in another table.

For example, an employee may have multiple compensation programs and a compensation program may belong to multiple employees.

Therefore, multiple rows in the employee table are associated with multiple rows in the compensation table. Hence, the relationship between employees and compensation programs is a many-to-many relationship.

Typically, relational databases do not implement a direct many-to-many relationship between two tables. Instead, it uses a third table, the join table, to establish two one-to-many relationships between the two tables and the join table.

The following diagram illustrates the many-to-many relationships in the database between the hr_employee and hr_compensation tables:

The hr_employee_compensations table is a join table. It has two foreign keys employee_id and compensation_id.

The employee_id foreign key references the id of the hr_employee table and the compensation_id foreign key references the id in the hr_compensation table.

Typically, you don’t need the id column in the hr_employee_compensations table as a primary key and use both employee_id and compensation_id as a composite primary key. However, Django always creates the id column as a primary key for the join table.

Also, Django creates a unique constraint that includes the employee_id and compensation_id columns. In other words, there will be no duplicate pairs of employee_id and compensation_id values in the hr_employee_compensations table.

To create a many-to-many relationship in Django, you use the ManyToManyField. For example, the following uses the ManyToManyField to create a many-to-many relationship between Employee and Compensation models:

# ...
class Compensation(models.Model):
    name = models.CharField(max_length=255)

    def __str__(self):
        return self.name


class Employee(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

    contact = models.OneToOneField(
        Contact,
        on_delete=models.CASCADE,
        null=True
    )

    department = models.ForeignKey(
        Department,
        on_delete=models.CASCADE
    )

    compensations = models.ManyToManyField(Compensation)

    def __str__(self):
        return f'{self.first_name} {self.last_name}'
Code language: Python (python)

How it works.

First, define a new Compensation model class that extends the models.Model class.

Second, add the compensations field to the Employee class. The compensations field uses the ManyToManyField to establish the many-to-many relationship between the Employee and Compensation classes.

To propagate the changes of the models to the database, you run the makemigrations command:

python manage.py makemigrationsCode language: CSS (css)

It’ll output something like this:

Migrations for 'hr':
  hr\migrations\0004_compensation_employee_compensations.py
    - Create model Compensation
    - Add field compensations to employeeCode language: plaintext (plaintext)

And execute the migrate command:

python manage.py migrateCode language: plaintext (plaintext)

Output:

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, hr, sessions
Running migrations:
  Applying hr.0004_compensation_employee_compensations... OK  Code language: plaintext (plaintext)

Django created two new tables hr_compensation and a join table hr_employee_compensations as follows:

Creating data

First, run the shell_plus command:

python manage.py shell_plusCode language: CSS (css)

Second, create three compensation programs including StockBonuses, and Profit Sharing:

>>> c1 = Compensation(name='Stock')
>>> c1.save()
>>> c2 = Compensation(name='Bonuses') 
>>> c2.save()
>>> c3 = Compensation(name='Profit Sharing')  
>>> c3.save()
>>> Compensation.objects.all()
<QuerySet [<Compensation: Stock>, <Compensation: Bonuses>, <Compensation: Profit Sharing>]>Code language: Python (python)

Third, get the employee with the first name John and last name Doe:

>>> e = Employee.objects.filter(first_name='John',last_name='Doe').first()
>>> e
<Employee: John Doe>Code language: Python (python)

Adding compensations to employees

First, enroll John Doe in the stock (c1) and bonuses (c2) compensation programs using the add() method of the compensations attribute and the save() method of the Employee object:

>>> e.compensations.add(c1)
>>> e.compensations.add(c2) 
>>> e.save()Code language: Python (python)

Second, access all compensations program of John Doe using the all() method of the compensations attribute:

>>> e.compensations.all()
<QuerySet [<Compensation: Stock>, <Compensation: Bonuses>]>Code language: Python (python)

As clearly shown in the output, John Doe has two compensation programs.

Third, enroll Jane Doe in three compensation programs including stock, bonuses, and profit sharing:

>>> e = Employee.objects.filter(first_name='Jane',last_name='Doe').first()
>>> e 
<Employee: Jane Doe>
>>> e.compensations.add(c1)
>>> e.compensations.add(c2) 
>>> e.compensations.add(c3) 
>>> e.save()
>>> e.compensations.all()
<QuerySet [<Compensation: Stock>, <Compensation: Bonuses>, <Compensation: Profit Sharing>]>Code language: Python (python)

Internally, Django inserted the ids of employees and compensations into the join table:

 id | employee_id | compensation_id
----+-------------+-----------------
  1 |           5 |               1
  2 |           5 |               2
  3 |           6 |               1
  4 |           6 |               2
  5 |           6 |               3
(5 rows)Code language: plaintext (plaintext)

Fourth, find all employees who were enrolled in the stock compensation plan using the employee_set attribute of the Compensation object:

>>> c1
<Compensation: Stock>
>>> c1.employee_set.all()
<QuerySet [<Employee: John Doe>, <Employee: Jane Doe>]>Code language: Python (python)

It returned two employees as expected.

Fifth, you can use the employee_set attribute to find all employees who have the profit-sharing compensation program:

>>> c3                   
<Compensation: Profit Sharing>
>>> c3.employee_set.all()
<QuerySet [<Employee: Jane Doe>]>Code language: Python (python)

It returned one employee.

Django allows you to query across the relationship. For example, you can find all employees who have the compensation with id 1:

>>> Employee.objects.filter(compensations__id=1) 
<QuerySet [<Employee: John Doe>, <Employee: Jane Doe>]>Code language: Python (python)

Or with the name "Profit Sharing":

>>> Employee.objects.filter(compensations__name="Profit Sharing") 
<QuerySet [<Employee: Jane Doe>]>Code language: Python (python)

Removing compensations from employees

To remove a compensation program from an employee, you use the remove() method of the compensations attribute of the Employee object. For example:

First, get the employee whose name is Jane Doe:

>>> e = Employee.objects.filter(first_name='Jane',last_name='Doe').first()
>>> e                                                                     
<Employee: Jane Doe>Code language: Python (python)

Second, remove the profit sharing compensation (c3) from Jane Doe and save the changes to the database:

>>> e.compensations.remove(c3)
>>> e.save()Code language: Python (python)

Third, get all the compensation programs of Jane Doe:

>>> e.compensations.all()
<QuerySet [<Compensation: Stock>, <Compensation: Bonuses>]>Code language: Python (python)

Now, Jane Doe has two compensation programs left.

Summary

  • In a many-to-many relationship, multiple rows in a table are associated with multiple rows in another table.
  • Relation databases use a join table to establish a many-to-many relationship between two tables.
  • Use ManyToManyField to model a many-to-many relationship between models in Django.
Follow Us On