Hi Mike,
Thanks! I followed your advice and indeed it does work as expected.
However I still get this warning:
|
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.
|
I have many m2m tables and there is a huge output of these warnings
every time which is super annoying. Is there a way to tell SA not to
complain about this and only this? I would still like to see other warnings.
Again the full code:
|
fromsqlalchemy
importcreate_engine,Column,Integer,Text,Table,ForeignKeyConstraint,ForeignKey,and_
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importforeign,relationship,Session,joinedload,remote
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'<Role {} {}>'.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_(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))
engine =create_engine('sqlite://')
# engine.echo = True
Base.metadata.create_all(engine)
session =Session(engine)
# Create our account
a1 =Account()
a2 =Account()
session.add(a1)
session.add(a2)
session.commit()
# Create roles
u_role =Role()
u_role.id =1
u_role.account_id =a1.id
u_role.name ='user'
session.add(u_role)
m_role =Role()
m_role.id =2
m_role.account_id =a1.id
m_role.name ='member'
session.add(m_role)
a2_role =Role()
a2_role.id =3
a2_role.account_id =a2.id
a2_role.name ='member'
session.add(a2_role)
session.commit()
# Create 1 user
u =User()
u.id =1
u.account_id =a1.id
u.name ='user'
# This does not work
u.roles =[u_role,m_role,a2_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).options(joinedload('roles')).first()
forr inu.roles:
print(r)
|
Thank you!
Alex.
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
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:
|
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:
|
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
<http://user.id>','user.account_id']),
ForeignKeyConstraint(['role_id','account_id'],['role.id
<http://role.id>','role.account_id']),
)
classRole(Base):
__tablename__ ='role'
id =Column(Integer,primary_key=True)
account_id =Column(Integer,ForeignKey('account.id
<http://account.id>'),primary_key=True)
name =Column(Text)
def__str__(self):
return'<Role {} {}>'.format(self.id,self.name)
classUser(Base):
__tablename__ ='user'
id =Column(Integer,primary_key=True)
account_id =Column(Integer,ForeignKey('account.id
<http://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
<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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
