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

问题描述：

给定这对类:

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来实现:

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

我想用 column_property 或 relationship 来做到这一点，而不是普通的 property.到目前为止，我所拥有的是:

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

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

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

答

好的，这是一个有效的尝试:

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 与 ThingInfos 连接起来
我正在研究如何编写此代码以使用 groubby

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

相关推荐