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())
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 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)", )
secondary=supplierAndGroup : Third table reference.
primaryjoin : Join conditions.
secondaryjoin : Secondary Join conditions
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) )
Actually this is the third table / Pivot table which hold the Supplier & SupplierGroup both table foreign and make relation between them