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 models.py file.
Python's __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 views.py):
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...