I've written a handful of primaryjoin and secondaryjoin attributes on Relationships. This mechanism is flexible, but it's error-prone, and I think that, at least for all the cases I've personally encountered, there could be a better way. As an example, I have:
thing = Table('thing', metadata, Column('thing_id', Integer, primary_key=True), Column('favorite_group_id', Integer), ForeignKeyConstraint(['thing_id', 'favorite_group_id'], ['rel.thing_id', 'rel.group_id'], use_alter=True, name='foobar')) group = Table('group', metadata, Column('group_id', Integer, primary_key=True)) rel = Table('rel', metadata, Column('group_id', Integer, ForeignKey('group.group_id', primary_key=True), Column('thing_id', Integer, ForeignKey('thing.thing_id'), primary_key=True)) IOW I have things and groups. The rel table is a many-to-many relation between things and groups. A thing also may have a favorite group; if so, there has to be a rel between that thing and its favorite group. I don't have a foreign key directly from favorite_group_id because the equivalent constraint is already implied by the existing foreign keys. Everything is straightforward to map, except the favorite_group relation on thing -- there's no foreign key. So I can set primary and primaryjoin, but here's a different suggestion: Add a couple of flags to ForeignKeyConstraint so that I can have three kinds of ForeignKeyConstraint: 1. The normal kind: the mapper setup and the DDL both see it. 2. A mapper-only ForeignKeyConstraint: the mapper will look at it to deduce join conditions, but there's no DDL. 3. A DDL-only ForeignKeyConstraint: There's DDL but the mapper won't see it. So I could do: thing = Table('thing', metadata, Column('thing_id', Integer, primary_key=True), Column('favorite_group_id', Integer), ForeignKeyConstraint(['thing_id', 'favorite_group_id'], ['rel.thing_id', 'rel.group_id'], use_alter=True, use_for_mapper=False, name='foobar'), ForeignKeyConstraint(['favorite_group_id'], ['group.group_id'], use_alter=True, emit_ddl=False, name='foobar')) And now the trivial mapper relations will all work with no fiddling. Thoughts? --Andy -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.