many-to-many-to-one?

[maqui...@gmail.com]

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