PFRF SQLAlchemy Many to Many Relationship

Let’s assume that we are going to make relationship between Supplier can have into Multiple SupplierGroup, such as Mango can belongsTo or has many Fruit SupplierGroup and Super Shop SupplierGroup.

  • SupplierGroup : This entity / model will have multiple supplier group

  • Supplier : This entity / model will also have multiple supplier

  • supplierAndGroup This entity / model will connect both of them.

For better understanding you can see the entity relationship topic


SupplierGroup

from pf_sqlalchemy.db.orm import Base, database


class SupplierGroup(Base):
    name = database.Column("name", database.String(200), nullable=False)
    description = database.Column("description", database.Text())


Supplier

from pf_sqlalchemy.db.orm import Base, database
from supplier.model.supplier_m2m_group import supplierAndGroup


class Supplier(Base):
    name = database.Column("name", database.String(200))
    supplierGroup = database.relationship(
        'SupplierGroup',
        secondary=supplierAndGroup,
        lazy='subquery',
        backref=database.backref('supplier', lazy=True),
        primaryjoin="and_(Supplier.id==supplier_m2m_group.c.supplier_id)",
        secondaryjoin="and_(SupplierGroup.id==supplier_m2m_group.c.supplier_group_id, SupplierGroup.isDeleted==False)",
    )
Here
  • secondary=supplierAndGroup : Third table reference.

  • primaryjoin : Join conditions.

  • secondaryjoin : Secondary Join conditions


supplierAndGroup

from pf_sqlalchemy.db.orm import database


supplierAndGroup = database.Table(
    'supplier_m2m_group',
    database.Column('supplier_id', database.BigInteger, database.ForeignKey('supplier.id'), primary_key=True),
    database.Column('supplier_group_id', database.BigInteger, database.ForeignKey('supplier_group.id'), primary_key=True)
)
Here

Actually this is the third table / Pivot table which hold the Supplier & SupplierGroup both table foreign and make relation between them