I have 2 database tables: Items and Purchases, and they are defined in ORM model following way:

class Items(db.Entity):
  pk = orm.PrimaryKey(int, auto=True)
  name = orm.Required(str)
  purchase = orm.Set('Purchases')
  ...


class Purchases(db.Entity):
  pk = orm.PrimaryKey(int, auto=True)
  dateTime = orm.Required(datetime, default=datetime.now())
  item = orm.Required(Items)
  ...

And I need to make Pony ORM query to get all items, and their last purchase today. If no purchases were made today, item still needs to be in response, but its purchase field must be NULL.

I tried following:

@orm.db_session
def getData(self):
  today_night = datetime.combine(date.today(), datetime.min.time())
  newData = orm.select((item, p) for item in Items for p in item.purchase if (p.dateTime == max(p.dateTime for p in item.purchase if p.dateTime >= today_night)))
  allData = orm.left_join((item1, p) for item1 in Items for item2, p in newData)
  return allData

newData contains only those items updated today, allData is expected to contain all items with maximum date value for those items presented in newData and NULL for those which are not. But when newData is empty I get empty allData, despite the Items table is not empty.