Using relationships

With relational database management systems in picture, we can now define how two models relate to each other. The databases support the modeling of different kinds of relationships between the two models, such as:

• One to One Relationships: These are the kind of relationships where a record from one model relates to only one record from another model. For example, a user in our user model has only one activation key mapped to it from our ActivationKey model. This is a one to one relationship.
• One to Many Relationships: These are the kind of relationships where a record from one model maps to multiple records from another model. For example, if we have a model named Bug, describing the bug entries, then we can say, a user record from our user model can map to a number of records in our Bug model, since the same user can file multiple bugs. This is a one-to-many relationship.
• Many to Many Relationships: These are the kind of relationships where a record from one model can map to more than one record from another model, which can indeed map back to multiple records in the first model. For example, a user record from our user model can be mapped to many bug records from our bug assignee model, and a record from our bug assignee model can map to multiple user records from our user model. This is a many to many relationship.

There are a few more relationships that can be expressed in a relational database management system, such as self-referencing relationships, but covering all of them is beyond the scope of this book.

Now, if we think back to the example of the optimized models we built for representing our users, we may recall that we utilized a foreign key relationship between our role model and user model, and also in our user model and activation key model.

Now let's try to imagine this. When we want to access the role data of a user, how do we do it? The following snippet gives an example of how we can do this:

Session = sessionmaker(bind=engine)
db_session = Session()
user_record = db_session.query(User).first()
role_id = user_record.role_id
role = db_session.query(Role).filter_by(id=role_id)

In this example, we first initialized the database session to perform the queries on the database. The next thing we did was to get the first user record from the user model. Our user model maps to the role model through the use of role_id, which is a foreign key to the id field inside the role model.

This allows us to query the information about the role by using its role id. Once we get the role id of the user from the user model, we can then issue a query to the role model to fetch the required record from it.

Now, there should be an easy way to achieve this effect, where we don't have to issue these two queries ourselves. Can our ORM help us somehow?

Yes, we can make our ORM solution do this work for us. But for that to work, we need to create a relationship between our role model and user model. So, let's redefine our user model to express this relation. The following code snippet shows this:

class User(Base):
  __tablename__ = 'users'

  id = Column(Integer, primary_key=True, autoincrement=True)
  first_name = Column(String, nullable=False)
  last_name = Column(String, nullable=False)
  username = Column(String(length=25), unique=True, nullable=False)
  email = Column(String(length=255), unique=True, nullable=False)
  password = Column(String(length=255), nullable=False)
  date_joined = Column(Date, default=datetime.now())
  user_role = Column(Integer, ForeignKey("roles.id"))
  role = relationship("Role")
  account_active = Column(Boolean, default=False)
  activation_key = Column(String(length=32))

  def __repr__(self):
    return "<User {}>".format(self.username)

In this code snippet, we added a new field named role. This field maps a relationship from our user record to a record in the role model. The filtering happens automatically based on the user_role column, which has a foreign key mapped to the id field of the role model.

Now, going back to the example where we queried our user model to fetch the user record, and then fetching our role record for the user, we can re-write it as shown in the following code snippet:

Session = sessionmaker(bind=engine)
db_session = Session()
user_record = db_session.query(User).first()
role = user_record.role

And there we go, we did not have to first retrieve the role_id key and then query the role model manually to fetch the role information about the user.

But, the interesting story is happening behind the scenes. To achieve this effect, what SQLAlchemy did was to utilize lazy loading techniques. When we queried our user model to fetch the record of one user, SQLAlchemy did retrieve the record for the user, but never made an effort to retrieve the record of the role to which the user was associated. We can poke around the fields that are present in user_record by running the following command:

dir(user_record.user_role)

By doing so, we would have seen that the role field was present. But until this time, the role field simply held an instance of the SQLAlchemy query object. This object just contains the query that will be used to fetch the role data of the associated role from the role model but does not contain any real data as of yet. This won't happen until we call the following code:

role = user_record.role

The query object from the role field in our user_record is executed and the role field is populated with some real data.

This technique is called lazy loading, as we will see in the next section.