SQLAlchemy-Continuum how to make changes made in many-to-many relationship present in changeset

Question

I'm trying to track changes in many-to-many relationship using SQLAlchemy-Continuum package.

Unfortunately only changes made to simple fields like db.String are present in the changeset.

How to make changes made to relationship fields present in the changeset?

There are some fragments in the docs concerning many-to-many relationships but the documentation lacks examples. https://sqlalchemy-continuum.readthedocs.io/en/latest/api.html#sqlalchemy_continuum.relationship_builder.RelationshipBuilder.build_association_version_tables

Any help and examples will be appreciated.

Answer 1

I got the same issue, and I think I found a solution. What I did was first creating actual classes of the many to many tables:

# Many to many relationship for users and accounts
user_account_mapping = Table(
    "user_accounts",
    Base.metadata,
    Column("user_id", ForeignKey("users.id"), primary_key=True),
    Column("account_id", ForeignKey("accounts.id"), primary_key=True),
)

# I added the following class:
class UserAccounts(Base):
    __versioned__ = {}
    __table__ = user_account_mapping
    __mapper_args__ = {"primary_key": [
        user_account_mapping.c.user_id, 
        user_account_mapping.c.account_id
    ]}

This way you can actually get the version_class of the junction table. In my case, if I add/delete an account, a record is added to the user_version table, the user_accounts table, but not in the accounts table (which makes sense, because we didn't change an account).

For SQLAlchemy Continuum this seems hard to actually join the right tables. I guess it somehow makes sense, because when do you need a version table and when not.

What you can do, is building a query yourself now you can get the version_class of the junction table. Obviously you could write your own query and just perform a SELECT query, but I like to keep using the ORM as much as possible.

A dirty solution:

from models.user import User
from models.account import Account
from models.mappings import UserAccounts

versioned_user = version_class(User)
versioned_useraccount = version_class(UserAccount)

results = ( 
    db.query(versioned_user, versioned_useraccount, Account)
        .join(versioned_useraccount, 
              and_(
                    versioned_user.id == versioned_useraccount.user_id,
                    versioned_user.transaction_id == versioned_useraccount.transaction_id,
              )
        )
        .join(Account, versioned_useraccount.account_id == Account.id).all()
)

# Map the results, so we don't have the same user object for each account it contains, 
# but just one per unique user with an "accounts" key which contains a list of all accounts.
user_account_mapping = {}
for versioned_user, versioned_useraccount, account in results:
    if versioned_user.transaction_id not in user_account_mapping:
        user_account_mapping[versioned_user.transaction_id] = {}
        user_account_mapping[versioned_user.transaction_id]["user"] = versioned_user
        user_account_mapping[versioned_user.transaction_id]["accounts"] = []

    user_account_mapping[versioned_user.transaction_id]["accounts"].append({
        "id": versioned_useraccount.user_id,
        "operation_type": versioned_useraccount.operation_type,
        "name": account.name
    })