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.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from datetime import date
base = declarative_base()
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)
e = create_engine('sqlite://', echo=True)
base.metadata.create_all(e)
db = Session(e)
db.add(RosterLine(line_id='work', group_id='staff'))
db.add(RosterLine(line_id='etc', group_id='manager'))
db.add(Enrolment(person_id='bob',
group_id='staff',
level_id='normal',
enrol_date=date(2010,1,1),
next_date=date(2011,1,1)))
db.add(Enrolment(person_id='bob',
group_id='staff',
level_id='better',
enrol_date=date(2011,1,1)))
db.add(Enrolment(person_id='bob',
group_id='manager',
level_id='special',
enrol_date=date(2012,1,1)))
db.add(Timesheet(person_id='bob',
line_id='work',
date=date(2012,3,4)))
db.flush()
db.commit()
t = db.query(Timesheet).first()
print t.enrolment
# want:
'''
select E.*
from roster_line L,
enrolment E
where L.line_id = 'work'
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)
and L.group_id = E.group_id
'''
