Sqlalchemy有效的子查询以获得最新值

Question

实体status属性的当前值可以将其作为该实体的 EntityHistory 表中的最新条目，即。

Entities (id) <- EntityHistory (timestamp, entity_id, value)

如何编写一个有效的SQLalchemy表达式，该表达式急切地从历史表中加载所有实体的当前值，而不会产生n 1个查询？

我尝试为我的模型编写属性，但是当我迭代它时，这会为每个（n 1）生成一个查询。据我所知，没有子查询就无法解决这个问题，这对我的数据库似乎仍然效率低下。

示例EntityHistory数据：

timestamp |entity_id| value
==========|=========|======
     15:00|        1|     x
     15:01|        1|     y
     15:02|        2|     x
     15:03|        2|     y
     15:04|        1|     z

因此，实体1的当前值将是z，对于实体2，它将为y。支持数据库是Postgres。

Answer 1

我认为您可以使用column_property沿其他列映射属性加载最新值作为Entities实例的属性：

from sqlalchemy import select
from sqlalchemy.orm import column_property
class Entities(Base):
    ...
    value = column_property(
        select([EntityHistory.value]).
        where(EntityHistory.entity_id == id).  # the id column from before
        order_by(EntityHistory.timestamp.desc()).
        limit(1).
        correlate_except(EntityHistory)
    )

当然也可以在查询中而不是column_property中使用一个子查询。

query = session.query(
    Entities,
    session.query(EntityHistory.value).
        filter(EntityHistory.entity_id == Entities.id).
        order_by(EntityHistory.timestamp.desc()).
        limit(1).
        label('value')
)                  

性能自然取决于适当的索引：

Index('entityhistory_entity_id_timestamp_idx',
      EntityHistory.entity_id,
      EntityHistory.timestamp.desc())

在某种程度上，这仍然是您可怕的n 1，因为查询使用每个行的子查询，但它隐藏在单程中的DB中。

另一方面，如果没有必要 value 作为Entities的属性，则在PostgreSQL中，您可以以与众不同的方式加入...按查询订购以获取最新值：

values = session.query(EntityHistory.entity_id,
                       EntityHistory.value).
    distinct(EntityHistory.entity_id).
    # The same index from before speeds this up.
    # Remember nullslast(), if timestamp can be NULL.
    order_by(EntityHistory.entity_id, EntityHistory.timestamp.desc()).
    subquery()
query = session.query(Entities, values.c.value).
    join(values, values.c.entity_id == Entities.id)

尽管在使用虚拟数据的有限测试中，如果每个实体都有值，那么子查询 - 输出列总是以显着的边距击败加入。另一方面，如果有数百万个实体和许多缺失的历史价值，那么左联接的速度更快。我建议您根据自己的数据进行测试，以更适合您的数据。为了随机访问单实体，鉴于该索引已经存在，相关的子查询更快。用于散装获取：测试。