SQLAlchemy: Multiple foreign keys to same table with compound primary key

Question

In SQLAlchemy, imagine we have a table Foo with a compound primary key, and Bar, which has two foreign key constrains linking it to Foo (each Bar has two Foo objects).

My problem is with the relationship function, which makes me repeat the information (in the primaryjoin) which I have already given in the ForeightKeyConstraint (violating DRY). Is there some other way of structuring this so that I don't have to repeat information? Some way of passing the relevant ForeignKeyConstraint to relationship?

class Foo(Base):
    __tablename__ = "Foo"
    id_1 = Column(Integer, primary_key=True)
    id_2 = Column(Integer, primary_key=True)

class Bar(Base):
    __tablename__ = "Bar"
    id = Column(Integer, primary_key=True)

    foo_1_id_1 = Column(Integer)
    foo_1_id_2 = Column(Integer)

    foo_2_id_1 = Column(Integer)
    foo_2_id_2 = Column(Integer)

    __table_args__ = (
            ForeignKeyConstraint(
                [foo_1_id_1,foo_1_id_2],
                [Foo.id_1,Foo.id_2]
                ),
            ForeignKeyConstraint(
                [foo_2_id_1,foo_2_id_2],
                [Foo.id_1,Foo.id_2]
                )
            )

    foo_1 = relationship(Foo,primaryjoin="(Bar.foo_1_id_1 == Foo.id_1) & (Bar.foo_1_id_2 == Foo.id_2)")
    foo_2 = relationship(Foo,primaryjoin="(Bar.foo_2_id_1 == Foo.id_1) & (Bar.foo_2_id_2 == Foo.id_2)")

Thanks.

Answer 1

The relationship(), as it is, cannot determine its full configuration. This is always the case when there is more than one way to refer to the related table.
In your example it seems like sqlalchemy could be smart enough to guess by the names of the columns, but this is not what does and it should not.

Although it might seem as if you have information repetition, in fact you are just being specific about your relationship configuration.

There is, in fact, option to specify foreign_keys in the relationship() configuration, but currently is serves somewhat different purpose, so you still will need to configure primaryjoin.

Answer 2

You can also use foreign_keys and a list of Foreign Keys.

See Multiple Join Paths

foo_1 = relationship(Foo, foreign_keys=[foo_1_id_1, foo_2_id_2])
foo_2 = relationship(Foo, foreign_keys=[foo_2_id_1, foo_2_id_2])