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.
