Custom secondary relation with composite primary keys

[Alex Plugaru]

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.

[mike bayer]

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)
)

> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Alex Plugaru]

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 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.

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:

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, joinedload, remote

        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()

for r in u.roles:
    print(r)

Thank you!

Alex.

[mike bayer]

try setting all but one of the four column targets as "foreign" so that
there isn't an overlapping "foreign" constraint:

roles = relationship(
Role,
secondary=roles_users,

primaryjoin=and_(id == foreign(roles_users.c.user_id),
account_id == foreign(roles_users.c.account_id)),
secondaryjoin=and_(Role.id == foreign(roles_users.c.role_id),
Role.account_id == roles_users.c.account_id))



alternatively, set the Python warnings filter to "ignore" for that
particular class / regular expression of warning.

On 05/04/2017 06:33 PM, Alex Plugaru wrote:
> 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://')

> 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.
>
>

[Alex Plugaru]

It worked! Thanks a lot!

On Friday, 28 April 2017 18:49:40 UTC-7, Alex Plugaru wrote:

[John Walker]

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

[Alex Plugaru]

Hi John,

Composite primary keys: id + account_id is for multi-tenancy and consistent and efficient indexes/joins. Multiple customers use the same postgres database and to the split of their data, the most efficient way that I know is to add a tenant id (in our case it's account_id) for each table.

Q: Why composite primary keys? Why not just primary key on id? 

Usually the cardinality of you tenant_id will be a lot smaller than the `id` of your table so that should make lookups much faster. Note that only works if you have the correct order in your constraint - meaning that tentant id should always be first: Example:  constraint your_table_pkey primary key (account_id, id) 

Q: Great, but why not a composite unique index?

That works too, primary key is just a unique index anyway behind the scenes, but it allows some tools (DataGrip SQL client for example) to work better by automatically generating the correct query for a join based on the primary/foreign key constraint for example.

Hope it helps, just remember that the order of columns in an index matters - if you know you're gonna have a lot of data it's not going to be easy to change a primary key on a table that already has a lot of data.