Sorry for the long subject line. I've got the following tables
(extraneous fields removed):
db.define_table('Members',\
Field("FirstName", "string"),\
Field("LastName", "string"))
db.define_table('MemberEvents',\
Field("MemberID", "integer"),\
Field("EventType", "string"),\
Field("EventDate", "string"))
The MemberEvents table holds multiple events for each member. Some
members may have no events; I want them to show up in the results as
well, but with Null/None values for the MemberEvents fields. What I'm
trying to do is join these two tables in such a way that I end up with
only the event that has the highest date that is less than a "target
date" parameter for each member. I've tried "groupby", but I can't
find a way to control which event is selected when the groups are
"collapsed". I've tried all kinds of things, but with no luck so
far. Here's my latest attempt (doesn't work):
maxDate = db.MemberEvents.EventDate.max()
results = db((db.MemberEvents.EventDate<=date)).select(\
db.Members.ALL,\
db.CaseManagers.ALL,\
maxDate,\
left=\
db.MemberEvents.on(db.Members.id == db.MemberEvents.MemberID),\
groupby=db.Members.id,\
having=(db.MemberEvents.EventDate==maxDate))
This gives an "Unknown column 'MemberEvents.EventDate' in 'having
clause'" error. Any help would be greatly appreciated. I'd even be
willing to paypal $30 to anyone who can solve this with the DAL (and
provide an explanation of the solution) -- without iterating over the
records in python (slow, I've got 10000+ event records). I need this
to work quickly with lots of records. I've been stuck on this for
days and have a deadline rapidly approaching.
Thanks in advance,
Joe G.
