surprising to see someone actually use corresponding_column. At the moment this is likely closest to the simplest way with relationship().
On Aug 14, 2013, at 10:03 PM, avdd <[email protected]> wrote: > 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 [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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
