It worked! Thanks a lot! On Friday, 28 April 2017 18:49:40 UTC-7, Alex Plugaru wrote: > > 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.