Select Item Having Maximum From Sqlalchemy Relationship

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(I

Solution 1:

Add an order_by clause to the relationship and this becomes trivial:

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]

or alternatively backref=backref('all_info', order_by='desc(ThingInfo.recorded_at)') and newest_info=thing.all_info[0].

Solution 2:

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