many-to-many-to-one?

6 views
Skip to first unread message

maqui...@gmail.com

unread,
Sep 30, 2021, 5:12:11 PMSep 30
to sqlalchemy
I'm trying to do something a little unusual, I'm open to alternative ideas for how to accomplish this as well but I think I need a 3 column mixer table with 3 foreign keys.

Right now I have many to many relationships between 3 tables, e.g.
a m2m b
b mbm c

however, I really something like (a_id, b_id, c_id) (again, could be a different way that i'm totally open to using) because only some Bs and Cs will be valid for an A, and so forth.

i still need to be able to associate many to many with each other, but with a discriminator (where) on the third column like a.bs where c=some_value

Is an association object capable of this or am I barking up the wrong tree?

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, Column, ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()

class Association(Base):
    __tablename__ = 'association'
    a_id = Column(ForeignKey('a.id'), primary_key=True)
    b_id = Column(ForeignKey('b.id'), primary_key=True)
    c_id = Column(ForeignKey('c.id'), primary_key=True)
    parent = relationship("A", back_populates="children")
    child = relationship("B", back_populates="parents")
    #cousin = relationship("C", back_populates="relatives") ???

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    # relatives = relationship("Association", back_populates="cousin")

Reply all
Reply to author
Forward
0 new messages