Create association of three or more tables

[fco...@gmail.com]

Hi all,

I would like to create some association of, at least, 3 tables User, Org (organisation) and Role : a User is given a Role on an Organisation.

So I began writing the following snippet but I am stucked as I do not know how I should write relationship() for User to be able to refer Orgs and Roles, Org to refer Users and Roles ...

I would like to avoid tricky code, do you have some advice on how I should do ?

# https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object
from datetime import datetime

from sqlalchemy import create_engine

from sqlalchemy import Column, ForeignKey, Integer, Text, DateTime

from sqlalchemy.orm import declarative_base, relationship

from sqlalchemy.orm.session import Session

Base = declarative_base()

class UserOrgRole(Base):

__tablename__ = "user_org_role"

user_id = Column(

ForeignKey("user.id", ondelete="CASCADE"), primary_key=True

)

org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True)

# Does it make sense ?

role_id = Column(

ForeignKey("role.id", ondelete="CASCADE"), primary_key=True

)

# I do not know what the relationship could back populate, org or role ???

user = relationship("User")

org = relationship("Org")

role = relationship("Role")

class User(Base):

__tablename__ = "user"

id = Column(Integer, primary_key=True)

username = Column(Text, index=True, nullable=False)

fullname = Column(Text, nullable=False)

account_type = Column(Text, nullable=False)

def __repr__(self):

return (

f"<User (username={self.username}, fullname={self.fullname}, "

f"account_type={self.account_type})>"

)

class Org(Base):

__tablename__ = "org"

id = Column(Integer, primary_key=True)

name = Column(Text, index=True, nullable=False)

slug = Column(Text, index=True, nullable=False)

created_at = Column(DateTime)

def __repr__(self):

return (

f"<Org (name={self.name}, slug={self.slug}, "

f"created_at={self.created_at})>"

)

class Role(Base):

__tablename__ = "role"

id = Column(Integer, primary_key=True)

name = Column(Text, index=True, nullable=False)

def __repr__(self):

return f"<Role (name={self.name})>"

if __name__ == "__main__":

engine = create_engine(

"sqlite:///association_object_ternary.db", echo=False

)

Base.metadata.drop_all(engine)

Base.metadata.create_all(engine)

with Session(engine) as session:

# create parent, append a child via association

u1 = User(

username="jlondon",

fullname="Jack London",

account_type="member",

)

o1 = Org(name="o1", slug="o1", created_at=datetime.utcnow())

owner = Role(name="owner")

uor1 = UserOrgRole()

uor1.user = u1

uor1.org = o1

uor1.role = owner

with session.begin():

session.add(u1)

session.add(o1)

session.add(owner)

session.add(uor1)

Thanks for your help and for this nice library which has a great documentation.
Françoise

[fco...@gmail.com]

Hi,

A colleague helped me to write the relationship() part, the working code with relationships is now :

class UserOrgRole(Base):

__tablename__ = "user_org_role"

user_id = Column(ForeignKey("user.id", ondelete="CASCADE"), primary_key=True)

org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True)

role_id = Column(ForeignKey("role.id", ondelete="CASCADE"), primary_key=True)

user = relationship("User", back_populates="orgsroles")

org = relationship("Org", back_populates="usersroles")

role = relationship("Role", back_populates="usersorgs")

class User(Base):

__tablename__ = "user"

id = Column(Integer, primary_key=True)

username = Column(Text, index=True, nullable=False)

fullname = Column(Text, nullable=False)

account_type = Column(Text, nullable=False)

orgsroles = relationship("UserOrgRole", back_populates="user")

class Org(Base):

__tablename__ = "org"

id = Column(Integer, primary_key=True)

name = Column(Text, index=True, nullable=False)

slug = Column(Text, index=True, nullable=False)

created_at = Column(DateTime)

usersroles = relationship("UserOrgRole", back_populates="org")

class Role(Base):

__tablename__ = "role"

id = Column(Integer, primary_key=True)

name = Column(Text, index=True, nullable=False)

usersorgs = relationship("UserOrgRole", back_populates="role")

Have a nice week
Françoise