On Feb 1, 2012, at 11:14 AM, Thijs Engels wrote: > In short; the Area class has a multi-column primary key (sesseion_id, > area_id). I am trying to create the relationship for area in the Child > class, however this would need the session_id which can be found in the > parent class. This was my last effort (again assuming I could treat the > parent class as a association proxy): > > area = relationship( > 'Area', > secondary='parents', > primaryjoin='children.c.parent_id==parents.c.id', > secondaryjoin='and_(parents.c.session_id==areas.c.session_id, > children.c.area_id==areas.c.id)', > viewonly=True > ) > > No able to figure out any foreign key relations if those would be > required. > > Any help and/or insight would be appreciated,
it's not very streamlined but a blunt approach using EXISTS seems to work: class Child(Base): __tablename__ = 'children' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parents.id')) area_id = Column(Integer) area = relationship(Area, foreign_keys=area_id, primaryjoin= (Area.area_id==area_id) & exists().where( (Parent.session_id==Area.session_id) & (parent_id==Parent.id)) ) There's two ways to navigate from Child to Area. What does it mean for Child.area_id to have a value that doesn't match on Child.parent_id->Parent.session_id->Area.session_id ? Is that essentially corrupt data ? That there's two ways to navigate implies that there are essentially two different relationships to Area - one is the straight relationship() on FK. The other is a long association proxy across Parent->Session->Area. Then the "area" you're looking for here is the intersection of those two. The primaryjoin we have above is basically doing the same thing in SQL, though if I wanted to be able to get at Child->Area in both ways independently I might build it out as the two relationships. If there's no need for two different ways to navigate from Child->Area, that only points out how this relational design is wrong - it's capable of storing corrupt data. Child.area_id might be just a denormalized "shortcut" to the longer chain across Parent. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.