create a non primary mapper to a select() that's against the Enrolment table joined to RosterLine (i.e. mapper(myselect.alias(), non_primary=True), then construct a relationship() to that mapper (viewonly=True of course).
at some point I should add an example of this technique, it's just the easiest way to deal with the relationship-across-any-number-of-tables use case. On Aug 14, 2013, at 2:30 PM, avdd <[email protected]> wrote: > Hello all > > Tried for hours to figure out the various relationship() options with no luck. > > Consider: > > > class Enrolment(base): > __tablename__ = 'enrolment' > person_id = Column(String, primary_key=True) > group_id = Column(String, primary_key=True) > enrol_date = Column(Date, primary_key=True) > level_id = Column(String, nullable=False) > next_date = Column(Date) > > def __repr__(self): > return 'Enrol(%s, %s, %s, %s)' % (self.person_id, > self.enrol_date, > self.group_id, > self.level_id) > > class RosterLine(base): > __tablename__ = 'roster_line' > line_id = Column(String, primary_key=True) > group_id = Column(String, nullable=False) > > > class Timesheet(base): > __tablename__ = 'timesheet' > id = Column(Integer, primary_key=True) > person_id = Column(String, nullable=False) > line_id = Column(String, nullable=False) > date = Column(Date, nullable=False) > > enrolment = relationship(Enrolment, > primaryjoin=lambda:( > (Timesheet.person_id == > foreign(Enrolment.person_id)) > & (Timesheet.date >= Enrolment.enrol_date) > & ((Timesheet.date < Enrolment.next_date) > | (Enrolment.next_date == None)) > #& (Timesheet.line_id == RosterLine.line_id) > #& (RosterLine.group_id == > Enrolment.group_id) > ), > # uselist=False, > viewonly=True) > > The relationship as it stands works correctly but I can't figure out the > magic words to introduce the intermediate join to RosterLine. > > The relationship should issue SQL like: > > > select E.* > from roster_line L, > enrolment E > where L.line_id = 'work' > and L.group_id = E.group_id > and E.person_id = 'bob' > and E.enrol_date <= '2012-03-04' > and (E.next_date > '2012-03-04' > or E.next_date is null) > > > > Eternally grateful for any help. > > Thanks. > > > > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > > <myrelation.py>
signature.asc
Description: Message signed with OpenPGP using GPGMail
