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.
