PFRF SQLAlchemy One to Many Relationship

Let’s assume that we are going to make relationship between Consumer has many address, such as Billing, Shipping etc, address.

  • Consumer : This is the paren entity / model

  • ConsumerAddress : This is the child entity / model


Consumer

from pf_sqlalchemy.db.orm import Base, database


class Consumer(Base):
    identifier = database.Column("identifier", database.String(200), nullable=False, unique=True)
    email = database.Column("email", database.String(200), unique=True)
    password = database.Column("password", database.String(500), nullable=False)
    addresses = database.relationship(
        'ConsumerAddress',
        backref='consumer',
        lazy=True,
        primaryjoin="and_(Consumer.id==ConsumerAddress.consumerId, ConsumerAddress.isDeleted==False)"
    )
Here
  • backref='consumer': Point the reference.

  • lazy=True: This means it will load the data on demand, Lazy has various implementation, please see those.

  • primaryjoin : Telling the joining condition, here we seeing if the address is not deleted then will join by Consumer id with ConsumerAddress consumerId.


ConsumerAddress

from consumer.data.consumer_enum import AddressType
from pf_sqlalchemy.db.orm import Base, database


class ConsumerAddress(Base):
    addressType = database.Column("address_type", database.Enum(AddressType), default=AddressType.REGULAR, nullable=False)
    consumerId = database.Column("consumer_id", database.BigInteger, database.ForeignKey('consumer.id'), nullable=False)
Here
  • database.ForeignKey('consumer.id') : It’s indicate database relational pointer or foreign key of the column consumer_id