try below.
db().select(\
db.Members.ALL,\
maxDate,\
left=\
db.MemberEvents.on(db.Members.id ==
db.MemberEvents.MemberID),\
having=(maxDate > date)|
(db.MemberEvents.EventDate==None), \
groupby=db.Members.id)
Pai
On Jun 21, 12:01 am, "Joseph S. Greenawalt" <[email protected]>
wrote:
> To clarify a bit, what I'm trying to do is very similar to
> this:http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-...
> But with two tables that need to be joined, with dates instead of
> prices, and with a "target date" that serves as a cutoff point for the
> highest date (no events with dates after the target date should end up
> in the results). I want one result per member in the resulting rows.
> And I'd prefer to do it through the DAL, because if I don't, I'll have
> to pack the results (list of tupples) into a list dictionaries, which
> is too time consuming for the amount of records that I'm dealing with
> (any iterative approach in python is out).
>
> Thanks,
> Joe G.
>
> On Jun 20, 11:50 pm, "Joseph S. Greenawalt" <[email protected]>
> wrote:
>
> > 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.
