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


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())


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(
        backref=database.backref('supplier', lazy=True),
        secondaryjoin="and_(, SupplierGroup.isDeleted==False)",
  • secondary=supplierAndGroup : Third table reference.

  • primaryjoin : Join conditions.

  • secondaryjoin : Secondary Join conditions


from pf_sqlalchemy.db.orm import database

supplierAndGroup = database.Table(
    database.Column('supplier_id', database.BigInteger, database.ForeignKey(''), primary_key=True),
    database.Column('supplier_group_id', database.BigInteger, database.ForeignKey(''), primary_key=True)

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