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'<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_(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 -
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
