Select item having maximum from sqlalchemy relatio

问题:

Given this pair of classes:

class Thing(Base):
    id = Column(Integer, primary_key=True)

class ThingInfo(Base):
    id = Column(Integer, primary_key=True)
    thing_id = Column(Integer, ForeignKey(Thing))
    recorded_at = Column(DateTime)

    thing = relationship(Thing, backref='all_info')

How can I define a property Thing.newest_info to achieve:

t = s.query(Thing).first()
newest_info = max(t.all_info, key=lambda i: i.recorded_at)
print newest_info

#equivalent to:
t = s.query(Thing).first()
print t.newest_info

I'd like to do this with a column_property or relationship, not a normal property. So far what I have is:

select([ThingInfo])
  .group_by(ThingInfo.thing)
  .having(func.max(ThingInfo.recorded_at))

But I can't figure out how to attach this as a propery of a single Thing object.

回答1:

Ok, here's a working attempt:

t = aliased(ThingInfo)
ThingInfo.is_newest = column_property(
    select([
        ThingInfo.recorded_at == func.max(t.recorded_at)
    ])
    .select_from(r)
    .where(t.thing_id == ThingInfo.thing_id)
)

Thing.newest_info = relationship(
    ThingInfo,
    viewonly=True,
    uselist=False,
    primaryjoin=(ThingInfo.thing_id == Thing.id) & ThingInfo.is_newest
)

Things I dislike about this:

• I'm having to specify how to join Things to ThingInfos in a second place
• I'm trying to work out how to write this to use a groubby