I once had a scenario where one business could be managed by multiple users, and each user could manage multiple businesses. To further complicate matters, only one user could be the Administrator of a business, which should ideally also be represented by the relationship.
Finding out how to represent that relationship was tricky as I couldn't find it in the SQLAlchemy docs, and StackOverflow was a mixed bag. This is what I pieced together:
from datetime import datetime as dt from app import db from sqlalchemy import Table, Column, Boolean, DateTime, Integer, \ SmallInteger, String, ForeignKey from sqlalchemy.orm import relationship from app import constants # Establish many-to-many relationship between users and (business) businesses class UserBusiness(db.Model): __tablename__ = 'userbusiness' user_id = Column(Integer, ForeignKey('users.id'), primary_key=True) business_id = Column(Integer, ForeignKey('businesses.id'), primary_key=True) administrator = Column(Boolean, default=False) business = relationship('Business', backref='users') user = relationship('User', backref='businesses') class User(db.Model): __tablename__ = 'users' id = Column(Integer, primary_key=True) email = Column(String(256), unique=True) name = Column(String(128)) password = Column(String(256)) last_auth = Column(DateTime, default=dt.utcnow) status = Column(SmallInteger, default=constants.NEW) # timestamps update_date = Column(DateTime, default=dt.utcnow, onupdate=dt.utcnow) insert_date = Column(DateTime, default=dt.utcnow) def __init__(self, email=None, name=None, password=None): self.email = email self.name = name self.password = password class Business(db.Model): __tablename__ = 'businesses' id = Column(Integer, primary_key=True) name = Column(String(256)) status = Column(SmallInteger, default=constants.NEW) # timestamps update_date = Column(DateTime, default=dt.utcnow, onupdate=dt.utcnow) insert_date = Column(DateTime, default=dt.utcnow) def __init__(self, name=None): self.name = name
In a Flask application, this would be contained your
__repr()__ and other common functions omitted for clarity.
To record a new User-Business relationship, first create the UserBusiness relationship, then add in the User and Business objects (if this were Flask, this'd likely be in
ub = UserBusiness(user=g.user, administrator=True) ub.business = Business(name='Acme Widgets Ltd') db.session.add(ub) db.session.commit()
To get a list of Businesses a user manages, you can do something like the following:
businesses = [ub.business for ub in g.user.businesses]
ub here represents the UserBusiness relation. You could then extend it to only get businesses which the user is an administrator of:
businesses = [ub.business for ub in g.user.businesses if ub.administrator]
It's all beautifully simple once you've done it successfully once or twice...