Hello Alex,
This is super old, so I don't have a lot of hope.
But I'm wondering if you could explain a line in your example text.
I'm trying to figure out if I need a data model similar to yours, but I'm
not sure.
Could you explain the datamodel/biz requirements reasoning behind this
quote "This is done for a few reasons, but let's say it's done to keep
everything consistent."
I would kill to know what the "few reasons" are, it might help me big time.
Thanks for any time/help.
John
On Friday, April 28, 2017 at 7:49:40 PM UTC-6, 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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/664e83f6-a4f3-4677-bf79-4912fb8af1d6%40googlegroups.com.
