Re: [sqlalchemy] Custom secondary relation with composite primary keys
On 04/28/2017 09:49 PM, 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
withrelationship(s):'User.roles'(copies user.account_id to
roles_users.account_id).Considerapplying viewonly=Trueto read-only
relationships,orprovide a primaryjoin condition marking writable columns
withthe 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:
the relationship is set up backwards.
Given:
class A(Base):
# ...
b = relationship(B)
This is A->B, primary->secondary looks like:
A -> primaryjoin -> secondary -> secondaryjoin -> B
Also "foreign" isn't needed here since all the "foreign" is already set
up on the tables. So correct set up is:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('account.id'),
primary_key=True)
name = Column(Text)
roles = relationship(
Role,
secondary=roles_users,
primaryjoin=and_(
id == roles_users.c.user_id,
account_id == roles_users.c.account_id),
secondaryjoin=and_(
Role.id == roles_users.c.role_id,
Role.account_id == roles_users.c.account_id)
)
The full example using SA 1.1.9:
|
fromsqlalchemy
importcreate_engine,Column,Integer,Text,Table,ForeignKeyConstraint,ForeignKey,and_
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importforeign,relationship,Session
Base=declarative_base()
classAccount(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']),
)
classRole(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''.format(self.id,self.name)
classUser(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()
forr inu.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] Custom secondary relation with composite primary keys
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 ''.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,
