[sqlalchemy] Relationship definition problem
I am currently facing a problem with a legacy database for which I am
creating a SQLAlchemy layer. With regards to mirroring the columns
everything goes well. However I am having difficulties defining a
relationship which is not many-to-many, but does use something which can
be compared with an association proxy (I think). The simplified model:
class Session(Base):
__tablename__ = 'sessions'
id = Column(Integer, primary_key=True)
class Area(Base):
__tablename__ = 'areas'
session_id = Column(Integer, ForeignKey('sessions.id'),
primary_key=True)
area_id = Column(Integer, primary_key=True)
name = Column(String)
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
session_id = Column(Integer, ForeignKey('sessions.id'))
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'))
area_id = Column(Integer)
area = relationship(???)
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,
Thijs
--
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.
Re: [sqlalchemy] Relationship definition problem
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.
