Эффективный подзапрос 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, так как запрос использует подзапрос на строку, но он скрыт в одном цикле передачи в БД.

Если, с другой стороны, имеет значение как свойство Entities не требуется, в Postgresql вы можете присоединиться с помощью запроса DISTINCT ON ... ORDER BY, чтобы получить последние значения:

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)

хотя в ограниченном тестировании с фиктивными данными столбец subquery-as-output-всегда всегда опережает объединение с заметным запасом, если у каждого объекта есть значения. С другой стороны, если были миллионы сущностей и много пропущенных значений истории, то ЛЕВОЕ СОЕДИНЕНИЕ было быстрее. Я бы порекомендовал протестировать ваши данные, какой запрос лучше подходит для ваших данных. Для произвольного доступа одного объекта с учетом наличия индекса коррелированный подзапрос выполняется быстрее. Для массовых загрузок: тест.