[web2py] Re: DAL question - highest date less than target date for each group of records w/ common field value

[Joseph S. Greenawalt]

Tried with this:

maxDate = db.MemberEvents.EventDate.max()
results =\
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)

Same error, "Unknown column 'MemberEvents.EventDate' in 'having
clause'".  I'm pretty sure it's related to my attempt to use the
maxDate in the "having" clause.  If I change maxDate to:

maxDate = db.MemberEvents.EventDate #.max()

It'll run without errors, but doesn't produce correct results.  I get
only a partial set of members w/ event dates mixed in that are after
the target date...

Thanks,
- Joe G.

On Jun 21, 11:10 am, Pai <pai.siwamut...@gmail.com> wrote:
> 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
>