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