Creating Relationships

In a data-driven website, it’s rare to have a table of information unrelated to information in other tables. This is primarily due to well-established database design best-practice.

Let’s take the venue field as an example. If you were only saving the venue name in the database, you could get away with repeating the name of the venue multiple times in your database records. But what about if you wanted to save more information for the venue?

Your venue records should also include an address, telephone number, website address and email. If you add these fields to your events table, you can see that you will end up with a lot of repeated information, not to mention the nightmare of ensuring you update all records if some venue information changes.

Database normalization is the process of designing your tables to minimize or eliminate data repetition. In simple terms, normalization is keeping related data in separate tables and linking tables via relationships (hence the name relational database).

Database normalization is also in keeping with Django’s Don’t Repeat Yourself (DRY) philosophy. So, as you would expect, Django makes creating relationships between tables of related information simple.

With our venue example, it would be good practice to have all the venue information in one table, and link to that information from the event table. We create this link in Django with a foreign key. Multiple events linking to one venue record is an example of a many-to-one relationship in relational database parlance. Looking at the relationship in the opposite direction, we get a one-to-many relationship, i.e., one venue record links to many event records.

Django provides QuerySet methods for navigating relationships in both directions—from one to many, and from many to one—as you will see shortly.

There is one other common database relationship that we need to explore, and that is the many-to-many relationship. An excellent example of a many-to-many relationship is the list of people who are going to an event. Each event can have many attendees, and each attendee can go to multiple events. We create a many-to-many relationship in Django with the ManyToManyField.

Let’s dispense with the theory and make some changes to our Event model, add a Venue model and add a MyClubUser model to our events app (changes in bold):

  1. # \myclub_root\events\models.py
  3. 1  from django.db import models
  4. 2  
  5. 3  class Venue(models.Model):
  6. 4      name = models.CharField('Venue Name', max_length=120)
  7. 5      address = models.CharField(max_length=300)
  8. 6      zip_code = models.CharField('Zip/Post Code', max_length=12)
  9. 7      phone = models.CharField('Contact Phone', max_length=20)
  10. 8      web = models.URLField('Web Address')
  11. 9      email_address = models.EmailField('Email Address')
  12. 10 
  13. 11     def __str__(self):
  14. 12        return self.name
  15. 13 
  16. 14 
  17. 15 class MyClubUser(models.Model):
  18. 16     first_name = models.CharField(max_length=30)
  19. 17     last_name = models.CharField(max_length=30)
  20. 18     email = models.EmailField('User Email')
  21. 19 
  22. 20     def __str__(self):
  23. 21         return self.first_name + " " + self.last_name
  24. 22 
  25. 23 
  26. 24 class Event(models.Model):
  27. 25     name = models.CharField('Event Name', max_length=120)
  28. 26     event_date = models.DateTimeField('Event Date')
  29. 27     venue = models.ForeignKey(Venue, blank=True, null=True, on_delete=models.CASCADE)
  30. 28     manager = models.CharField(max_length = 60)
  31. 29     attendees = models.ManyToManyField(MyClubUser, blank=True)
  32. 30     description = models.TextField(blank=True)
  33. 31 
  34. 32     def __str__(self):
  35. 33         return self.name

Note the order of the classes. Remember, this is Python, so the order of the models in your models.py file matters. The new model classes need to be declared before the Event model for the Event model to be able to reference them.

The Venue model is very similar to the Event model, so you should find it easy to understand. Notice how the Venue model uses two new model fields: URLField and EmailField.

This is another cool feature of Django’s models—while at the database level these fields are no different than Django CharField’s (they’re all saved as varchar’s in SQLite), Django’s models provide built-in validation for specialized fields like URLs and email addresses.

The MyClubUser model should also be straightforward. At the moment, it’s a simple user model that records the user’s name and email address. We’ll be expanding this model to create a custom user model in Chapter 14.

There are two important changes to the Event model:

  1. In line 27, I have changed the venue field type from a CharField to a ForeignKey field. The first argument is the name of the related model. I have added blank=True and null=True to allow a new event to be added without a venue assigned. The on_delete argument is required for foreign keys. CASCADE means that if a record is deleted, all related information in other tables will also be deleted.
  2. In line 29, I have added the attendees field. attendees is a Django ManyToManyField which has two arguments—the related model (MyClubUser), and blank set to True so an event can be saved without any attendees.

Before we add the new models to the database, make sure you delete the records from the Event table. If you don’t, the migration will fail as the old records won’t be linked to the new table. Go back to the Django interactive shell and delete all event records like so:

  1. (env_myclub) ...\myclub_root> python manage.py shell
  2. ...
  3. (InteractiveConsole)
  4. >>> from events.models import Event
  5. >>> Event.objects.all().delete()
  6. (4, {'events.Event': 4}) #Total records deleted. May be different for you.
  7. >>>exit()
  8. (env_myclub) ...\myclub_root>

Don’t forget to exit the interactive shell when you’re done (exit() or CTRL-Z).

Now, let’s add our new model to the database:

  1. (env_myclub) ...\myclub_root> python manage.py check
  2. System check identified no issues (0 silenced).
  3. (env_myclub) ...\myclub_root> python manage.py makemigrations events
  4. Migrations for 'events':
  5.   events\migrations\0002_auto_20200522_1159.py
  6.     - Create model Venue
  7.     - Create model MyClubUser
  8.     - Alter field venue on event
  9.     - Add field attendees on event
  10. (env_myclub) ...\myclub_root> python manage.py migrate
  11. Operations to perform:
  12. Apply all migrations: admin, auth, contenttypes, events, sessions
  13. Running migrations:
  14. Applying events.0002_auto_20200522_1159... OK
  15. (env_myclub) ...\myclub_root>

After running the migration, you can see that Django has added the events_venue, and events_myclubuser tables, and the venue field in the events_event table has been renamed venue_id and is now an integer field (Figure 4.10).

Creating Relationships - 图1

Figure 4.10: Django has added a new table and created a relationship with the event table. Did you notice there’s no attendees field, but there is an events_event_attendees table?

If you look at the event table schema (you can also do this with the sqlmigrate command you used earlier in the chapter) you can see the SQL Django uses to create the relationship:

  1. CREATE TABLE "events_event" (
  2.     "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  3.     "name" varchar(120) NOT NULL,
  4.     "event_date" datetime NOT NULL,
  5.     "manager" varchar(60) NOT NULL,
  6.     "description" text NOT NULL,
  7.     "venue_id" integer NOT NULL 
  8.         REFERENCES "events_venue" ("id")
  9.         DEFERRABLE INITIALLY DEFERRED
  10. )

Notice there is no reference to the attendees list in this SQL statement, nor is there an attendees field in the table.

This is because Django handles many-to-many relationships by creating an intermediate table containing each relationship in a simple event_id and myclubuser_id data pair (Figure 4.11).

Creating Relationships - 图2

Figure 4.11: Django records many-to-many relationships in an intermediate table.

Looking at the output from sqlmigrate, you can see Django is not only creating the relationships between the events table and myclubuser table, but it also sets up several indexes to make search faster:

  1. CREATE TABLE "events_event_attendees" (
  2.     "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  3.     "event_id" integer NOT NULL 
  4.         REFERENCES "events_event" ("id") 
  5.         DEFERRABLE INITIALLY DEFERRED, 
  6.     "myclubuser_id" integer NOT NULL 
  7.         REFERENCES "events_myclubuser" ("id") 
  8.         DEFERRABLE INITIALLY DEFERRED
  9.     );
  10. CREATE UNIQUE INDEX
  11.     "events_event_attendees_event_id_myclubuser_id_d3b4e7a8_uniq" ON
  12.     "events_event_attendees" ("event_id", "myclubuser_id");
  13. CREATE INDEX 
  14.     "events_event_attendees_event_id_45694efb" ON
  15.     "events_event_attendees" ("event_id");
  16. CREATE INDEX 
  17.     "events_event_attendees_myclubuser_id_caaa7d67" ON
  18.     "events_event_attendees" ("myclubuser_id");

As I mentioned earlier, this SQL will be different for each database. The key takeaway here is Django’s migrations take care of creating relationships in your database at the model level without you having to care about the underlying database structure.

Did you also notice how Django took care of updating the event table for you? Cool, huh?

Working With Related Objects

Because of the bi-directional nature of database relationships, and the need to maintain referential integrity, the basic database actions work differently with related objects.

For example, let’s try to add a new event in the Django interactive interpreter using the same code from the start of the chapter:

  1. >>> from events.models import Event
  2. >>> from datetime import datetime, timezone
  3. >>> event_date = datetime(2020,6,10,12,0, tzinfo=timezone.utc)
  4. >>> event1 = Event(
  5. ...     name="Test Event1",
  6. ...     event_date=event_date,
  7. ...     venue="test venue", 
  8. ...     manager="Bob"
  9. ... )
  10. Traceback (most recent call last):
  11. # ...
  12. ValueError: Cannot assign "'test venue'": "Event.venue" must be a "Venue" instance.
  13. >>>

I’ve removed the rest of the traceback from this code to keep things simple—so what went wrong?

We have created a relationship between the event table and the venue table, so Django expects us to pass an instance of a Venue object, not the name of the venue.

This is one way Django maintains referential integrity between database tables— for you to save a new event, there must be a corresponding venue record in the venue table.

So, let’s create a new venue. I am using the save() method here, but you could also use the create() shortcut method:

  1. >>> from events.models import Venue
  2. >>> venue1 = Venue(
  3.         name="South Stadium",
  4. ...     address="South St",
  5. ...     zip_code="123456",
  6. ...     phone="555-12345",
  7. ...     web="southstexample.com",
  8. ...     email_address="southst@example.com"
  9.     )
  10. >>> venue1.save()
  11. >>>

Now we can create an event using the venue instance (venue1) we just created, and the record should save without error:

  1. >>> event1 = Event(
  2. ...     name="Test Event1",
  3. ...     event_date=event_date,
  4. ...     venue=venue1, 
  5. ...     manager="Bob"
  6. ... )
  7. >>>  event1.save()
  8. >>>

Accessing Foreign Key Values

Due to the relationship created between the event table and the venue table, when you access the ForeignKey field from an instance of the Event, Django returns an instance of the related Venue object:

  1. >>> event1.venue
  2. <Venue: South Stadium>

You can also access the fields of the related model object with the dot operator:

  1. >>> event1.venue.web
  2. 'southstexample.com'

This works in the opposite direction, but because of the asymmetrical nature of the relationship, we need to use Django’s <object>_set() method. <object>_set() returns a QuerySet, for example, event_set() will return all the events taking place at a particular venue:

  1. >>> venue1.event_set.all()
  2. <QuerySet [<Event: Test Event1>]>

We are using the all() method here, but as <object>_set() returns a QuerySet, all Django’s regular QuerySet slicing and filtering methods will work.

Accessing Many-to-Many Values

Accessing many-to-many values works the same as accessing foreign keys, except Django returns a QuerySet, not a model instance. So you can see how this works, let’s first create a new user:

  1. >>> from events.models import MyClubUser
  2. >>> MyClubUser.objects.create(
  3.         first_name="Joe",
  4.         last_name="Smith",
  5.         email="joesmith@example.com"
  6.     )
  7. <MyClubUser: Joe Smith>

When we added the ManyToManyField to our Event model, a special model manager class called RelatedManager becomes available. RelatedManager has a few useful methods; in this example, we will use the add() method to add an attendee to an event:

  1. >>> attendee = MyClubUser.objects.get(first_name="Joe", last_name="Smith")    
  2. >>> event1.attendees.add(attendee)

You can also use the create() shortcut method to add a user and sign them up for an event in one step:

  1. >>> event1.attendees.add(
  2. ...     MyClubUser.objects.create(
  3. ...             first_name="Jane",
  4. ...             last_name="Doe",
  5. ...             email="janedoe@example.com"
  6. ...     )
  7. ... )

As they are QuerySets, accessing many-to-many records uses the same QuerySet methods as regular model fields:

  1. >>> event1.attendees.all()
  2. <QuerySet [<MyClubUser: Joe Smith>, <MyClubUser: Jane Doe>]>

And, to follow the relationship in the opposite direction, you use the <object>_set() method:

  1. >>> attendee.event_set.all()
  2. <QuerySet [<Event: Test Event1>]>