Thanks for the quick response! After much fiddling I got it working using alias(), foreign() and corresponding_column(). It seems to get the right results.
Is this the simplest, right approach? joined = Enrolment.__table__.join(RosterLine, Enrolment.group_id==RosterLine.group_id).alias() cc = joined.corresponding_column secmapper = mapper(Enrolment, joined, non_primary=True) # ... enrolment = relationship(secmapper, primaryjoin=lambda:( (Timesheet.line_id == foreign(cc(RosterLine.line_id))) & (Timesheet.person_id == cc(Enrolment.person_id)) & (Timesheet.date >= cc(Enrolment.enrol_date)) & ((Timesheet.date < cc(Enrolment.next_date)) | (cc(Enrolment.next_date) == None)) ), uselist=False, viewonly=True) On Thursday, 15 August 2013 04:30:12 UTC+10, avdd 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 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. For more options, visit https://groups.google.com/groups/opt_out.