python - 从 sqlalchemy 关系中选择具有最大值的项目

Question

给定这对类:

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')

如何定义属性 Thing.newest_info 来实现:

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

我想用 column_property 或 relationship 来实现，而不是普通的 property。到目前为止，我所拥有的是:

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

但我不知道如何将其附加为单个 Thing 对象的属性。

Answer 1

添加 order_by关系的条款，这变得微不足道:

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

    thing = relationship(Thing, backref=backref('all_info', order_by='ThingInfo.recorded_at')

thing = session.query(Thing).get(id)
newest_info = thing.all_info[-1]

或者 backref=backref('all_info', order_by='desc(ThingInfo.recorded_at)')和 newest_info=thing.all_info[0] .