how to get the version of a set of object at a given point in time / transaction id

[chicco785]

let's suppose I have a different set of transactions affecting different tables:

• transaction 1 affects object with id myObject in table A
• transaction 2 affects object with id myObject2 in table A
• transaction 3 affects object with id myObject3 in table B

how can i query all the objects values in table A and B at the time point of transaction 2?

thx :)

[marksteward]

It's not really aimed for that use, but you can do something like this:

from sqlalchemy import select, func
from sqlalchemy.orm import aliased
from sqlalchemy_continuum import version_class
from sqlalchemy_continuum.operation import Operation

AVersion = version_class(A)

TRANSACTION_ID = 2

latest_a_versions = select(
  AVersion.id,
  func.max(AVersion.transaction_id).label('transaction_id'),
).filter(
  AVersion.transaction_id <= TRANSACTION_ID
).group_by(AVersion.id).subquery()

a_versions = session.execute(
  select(AVersion).join_from(AVersion, latest_a_versions, and_(
    AVersion.id == latest_a_versions.c.id,
    AVersion.transaction_id == latest_a_versions.c.transaction_id
  )).filter(
    AVersion.operation_type != Operation.DELETE
  )
).all()

Replace AVersion with BVersion for the other table, or use get_primary_keys in a function that builds a query like the above automatically.

You can also use the Activity or TransactionChangesPlugin to help navigate transactions.

There's also similar code in many_to_one_criteria (don't forget that once you have a Version you can use a relationship to get other objects) and the reverter.