Implementing Relationships

#

In order to store who created the Product, we need to add a field called creator_id to Product as a foreign key that references the ID field of User.

This field should be stored as an integer (since the User ID is an integer type). The second argument to db.column will be the constraint we are adding, which is db.ForeignKey.

The ForeignKey constraint takes in the name of the table we are referencing and the field as a string, separated by a dot.

class Product(db.Model):
    ...
    creator_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Now whenever we have an instance of Product, we can access the the associated user through the creator attribute

>>> prod = Product.query.first()
>>> prod
<Product 1>
>>> prod.creator
<User 2>

In addition to being able to access the User object through creator, we can also pass in User objects to queries and when creating Product records.

>>> user = User.query.first()
>>> prod = Product(name='Book', description='a book', creator=user)
# Equivalent to Product(name='book', description='a book', creator_id=user.id)
>>> db.session.add(prod)
>>> db.session.commit()
>>> Product.query.filter_by(creator=user).all()
[<Product 1>]
# Equivalent to Product.query.filter_by(creator_id=user.id).all()

The creator attribute, however, is just one side of the relationship. The relationship between Users and Products can be described as a one-to-many relationship, where one User is the creator for many Products.

Right now, we haven't told SQLALchemy that there is an attribute that belongs on the User for the "one to many" relationship. In order to tell SQLAlchemy that there is a "one to many" relationship between User and Product, we have to declare how the relationship works on each model. SQLAlchemy provides a relationship method that we can use within each model, that provides a way to declare what model the relationship is linked to as the first argument. The parameter back_populates denotes which attribute the relationship will be accessible through on the other model. For User and Product, you can see how the back_populates argument, references the attribute on the other model.

class User(UserMixin, db.Model):
    ...
    products = db.relationship("Product", back_populates='creator')

class Product(db.Model):
    creator_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    ...
    creator = db.relationship("User", uselist=False, back_populates="products")

By default, the relationship method, assumes that there may be many records on this relationship, but if we only expect there to be one record (in this case, a Product will only have one creator), we also need to pass in uselist=False. If we wanted to create a many-to-many relationship, you would not need to include the uselist=False argument.

Now that the relationship is declared, we can access the products created by a user easily.

>>> prod = Product(name="Sample", description="test", creator_id=1)
>>> prod.creator
<User 1>
>>> db.session.add(prod)
>>> db.session.commit()
>>> User.query.find(1).products
[<Product 1>]
>>> prod.creator.email
"[email protected]"

Maintainability Tip: Instead of declaring the relationship on both models, SQLAlchemy provides a backref method, which allow you to just declare the relationship on one side. While it's easier, it trades off legibility of the relationship. You would have to look at every other model to figure out which attributes/relationships are available on any particular model. By explicitly declaring the relationship, it's easier for other developers to understand what relationships are available by glancing at the model.

In addition to accessing relationships through attributes, you can set or edit relationships through the relationship attributes.

>>> prod.creator = User.query.find(2)
>>> prod.creator
<User 2>
>>> db.session.add(prod)
>>> db.session.commit()
>>> prod.creator_id
2

We will also want to setup a relationship between a User and a Store.

class User(UserMixin, db.Model):
    ...
    store = db.relationship("Store", uselist=False, back_populates='user')

class Store(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    products = db.relationship("Product", back_populates='store')
    user = db.relationship("User", uselist=False, back_populates="store")

    @validates('name')
    def validate_name(self, key, name):
        if len(name.strip()) <= 3:
            raise ValueError('needs to have a name')
        return name

At this point, your models.py file should look like this.

from sqlalchemy.orm import validates
from flask_login import UserMixin
from werkzeug.security import generate_password_hash