Hello,
There are 3 tables: `*Account*`, `*Role*`, `*User*`. Both `*Role*` and `
*User*` have a foreign key `*account_id*` that points to `*Account*`.
A user can have multiple roles, hence the `*roles_users*` table which acts
as the secondary relation table between `*Role*` and `*User*`.
The `*Account*` table is a tenant table for our app, it is used to separate
different customers.
Note that all tables have (besides `*Account*`) have composite primary keys
with `*account_id*`. This is done for a few reasons, but let's say it's
done to keep everything consistent.
Now if I have a simple secondary relationship (`*User.roles*` - the one
that is commented out) all works as expected. Well kind of.. it throws a
legitimate warning (though I believe it should be an error):
SAWarning: relationship 'User.roles' will copy column role.account_id to
column roles_users.account_id, which conflicts with relationship(s):
'User.roles' (copies user.account_id to roles_users.account_id). Consider
applying viewonly=True to read-only relationships, or provide a primaryjoin
condition marking writable columns with the foreign() annotation.
That's why I created the second relation `*User.roles*` - the one that is
not commented out. Querying works as expected which has 2 conditions on
join and everything. However I get this error when I try to save some roles
on the user:
sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for source
column 'roles_users.role_id'; mapper 'Mapper|User|user' does not map this
column. Try using an explicit `foreign_keys` collection which does not
include destination column 'role.id' (or use a viewonly=True relation).
As far as I understand it, SA is not able to figure out how to save the
secondary because it has a custom `*primaryjoin*` and `*secondaryjoin*` so
it proposes to use `*viewonly=True*` which has the effect of just ignoring
the roles relation when saving the model.
The question is how to save the roles for a user without having to do it by
hand (the example is commented out in the code). In the real app we have
many secondary relationships and we're saving them in many places. It would
be super hard to rewrite them all.
Is there a solution to keep using `*User.roles = some_roles*` while keeping
the custom `*primaryjoin*` and `*secondaryjoin*` below?
The full example using SA 1.1.9:
from sqlalchemy import create_engine, Column, Integer, Text, Table,
ForeignKeyConstraint, ForeignKey, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import foreign, relationship, Session
Base = declarative_base()
class Account(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
roles_users = Table(
'roles_users', Base.metadata,
Column('account_id', Integer, primary_key=True),
Column('user_id', Integer, primary_key=True),
Column('role_id', Integer, primary_key=True),
ForeignKeyConstraint(['user_id', 'account_id'], ['user.id',
'user.account_id']),
ForeignKeyConstraint(['role_id', 'account_id'], ['role.id',
'role.account_id']),
)
class Role(Base):
__tablename__ = 'role'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('account.id'), primary_key=True)
name = Column(Text)
def __str__(self):
return '<Role {} {}>'.format(self.id, self.name)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('account.id'), primary_key=True)
name = Column(Text)
# This works as expected: It saves data in roles_users
# roles = relationship(Role, secondary=roles_users)
# This custom relationship - does not work
roles = relationship(
Role,
secondary=roles_users,
primaryjoin=and_(foreign(Role.id) == roles_users.c.role_id,
Role.account_id == roles_users.c.account_id),
secondaryjoin=and_(foreign(id) == roles_users.c.user_id,
account_id == roles_users.c.account_id))
engine = create_engine('sqlite:///')
engine.echo = True
Base.metadata.create_all(engine)
session = Session(engine)
# Create our account
a = Account()
session.add(a)
session.commit()
# Create 2 roles
u_role = Role()
u_role.id = 1
u_role.account_id = a.id
u_role.name = 'user'
session.add(u_role)
m_role = Role()
m_role.id = 2
m_role.account_id = a.id
m_role.name = 'member'
session.add(m_role)
session.commit()
# Create 1 user
u = User()
u.id = 1
u.account_id = a.id
u.name = 'user'
# This does not work
# u.roles = [u_role, m_role]
session.add(u)
session.commit()
# Works as expected
i = roles_users.insert()
i = i.values([
dict(account_id=a.id, role_id=u_role.id, user_id=u.id),
dict(account_id=a.id, role_id=m_role.id, user_id=u.id),
])
session.execute(i)
# re-fetch user from db
u = session.query(User).first()
for r in u.roles:
print(r)
FYI: I posted this on SO as well, but I haven't gotten a response there yet
so trying here too:
https://stackoverflow.com/questions/43690944/sqalchemy-custom-secondary-relation-with-composite-primary-keys
Hope it's ok.
Thank you for your help,
Alex.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
