many-many model join with SQLAlchemy

Question

I'm having the worst time trying to get a many-many join to work using models in SQLAlchemy. I've found lots of examples online, but I can't ever seem to figure out if their strings represent the column names they'd like versus what the database tables actually have, or they're using Table instead of a declarative model, or something else is different and their example just doesn't work. I currently have the following setup:

Database tables TAG_TEST, TAG, and TEST
TAG_TEST has TS_TEST_ID, TG_TAG_ID, and TG_TYPE (foreign keys)
TAG has TG_TAG_ID and TG_TYPE
TEST has TS_TEST_ID

I have the following models:

class Test(Base):
    from .tag import Tag
    from .tag_test import TagTest

    __tablename__ = u'TEST'
    id = Column(u'TS_TEST_ID', INTEGER(), primary_key=True, nullable=False)
    ...
    tags = relationship(Tag, secondary='TAG_TEST')

class Tag(Base):
    from .tag_test import TagTest
    __tablename__ = "TAG"
    id = Column(u'TG_TAG_ID', INTEGER(), primary_key=True, nullable=False)
    type = Column(u'TG_TYPE', VARCHAR(25))
    ...
    tests = relationship("Test", secondary='TAG_TEST')

class TagTest(Base):
    __tablename__ = u'TAG_TEST'
    tagID = Column(u'TG_TAG_ID', INTEGER(), ForeignKey("TAG.TG_TAG_ID"), primary_key=True, nullable=False)
    testID = Column(u'TS_TEST_ID', INTEGER(), ForeignKey("TEST.TS_TEST_ID"), primary_key=True, nullable=False)
    tagType = Column(u'TG_TYPE', VARCHAR(50), ForeignKey("TAG.TG_TYPE"), primary_key=True, nullable=False)
    ...
    tag = relationship("Tag", backref="testLinks")
    test = relationship("Test", backref="tagLinks")

Currently I'm getting the following error:

ArgumentError: Could not determine join condition between parent/child tables on relationship Tag.tests. Specify a 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed as well.

What am I missing/doing wrong?

Sarah Vessels Sarah Vessels · Accepted Answer · 2011-06-16T21:01:04

The tricky part with mine was the composite foreign key to the Tag table. Here's my setup:

class TagTest(Base):
    __table_args__ = (ForeignKeyConstraint(['TG_TAG_ID', 'TG_TYPE'],
        ['TAG.TG_TAG_ID', 'TAG.TG_TYPE']), {})
    tagID = Column(u'TG_TAG_ID', INTEGER(), primary_key=True, nullable=False)
    testID = Column(u'TS_TEST_ID', INTEGER(), ForeignKey("TEST.TS_TEST_ID"), primary_key=True, nullable=False)
    tagType = Column(u'TG_TYPE', VARCHAR(50), primary_key=True, nullable=False)
    tag = relationship(Tag, backref="testLinks")
    test = relationship(Test, backref="tagLinks")

class Tag(Base):
    tests = relationship("Test", secondary="TAG_TEST")

Then to access the tests a tag has, I can do myTag.tests. To access the tags a test has, I can do myTest.tagLinks and then access .tag on each object in the .tagLinks property. Not as neat as I'd like, but it works.

many-many model join with SQLAlchemy

1 Answers