Generic relationships¶
Generic relationship is a form of relationship that supports creating a 1 to many relationship to any target model.
from sqlalchemy_utils import generic_relationship
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
class Event(Base):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
# This is used to discriminate between the linked tables.
object_type = sa.Column(sa.Unicode(255))
# This is used to point to the primary key of the linked row.
object_id = sa.Column(sa.Integer)
object = generic_relationship(object_type, object_id)
# Some general usage to attach an event to a user.
user = User()
customer = Customer()
session.add_all([user, customer])
session.commit()
ev = Event()
ev.object = user
session.add(ev)
session.commit()
# Find the event we just made.
session.query(Event).filter_by(object=user).first()
# Find any events that are bound to users.
session.query(Event).filter(Event.object.is_type(User)).all()
Inheritance¶
class Employee(Base):
__tablename__ = 'employee'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(50))
type = sa.Column(sa.String(20))
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'employee'
}
class Manager(Employee):
__mapper_args__ = {
'polymorphic_identity': 'manager'
}
class Engineer(Employee):
__mapper_args__ = {
'polymorphic_identity': 'engineer'
}
class Activity(Base):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
object_type = sa.Column(sa.Unicode(255))
object_id = sa.Column(sa.Integer, nullable=False)
object = generic_relationship(object_type, object_id)
Now same as before we can add some objects:
manager = Manager()
session.add(manager)
session.commit()
activity = Activity()
activity.object = manager
session.add(activity)
session.commit()
# Find the activity we just made.
session.query(Event).filter_by(object=manager).first()
We can even test super types:
session.query(Activity).filter(Event.object.is_type(Employee)).all()
Abstract base classes¶
Generic relationships also allows using string arguments. When using generic_relationship with abstract base classes you need to set up the relationship using declared_attr decorator and string arguments.
class Building(Base):
__tablename__ = 'building'
id = sa.Column(sa.Integer, primary_key=True)
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
class EventBase(Base):
__abstract__ = True
object_type = sa.Column(sa.Unicode(255))
object_id = sa.Column(sa.Integer, nullable=False)
@declared_attr
def object(cls):
return generic_relationship('object_type', 'object_id')
class Event(EventBase):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
Composite keys¶
For some very rare cases you may need to use generic_relationships with composite primary keys. There is a limitation here though: you can only set up generic_relationship for similar composite primary key types. In other words you can’t mix generic relationship to both composite keyed objects and single keyed objects.
from sqlalchemy_utils import generic_relationship
class Customer(Base):
__tablename__ = 'customer'
code1 = sa.Column(sa.Integer, primary_key=True)
code2 = sa.Column(sa.Integer, primary_key=True)
class Event(Base):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
# This is used to discriminate between the linked tables.
object_type = sa.Column(sa.Unicode(255))
object_code1 = sa.Column(sa.Integer)
object_code2 = sa.Column(sa.Integer)
object = generic_relationship(
object_type, (object_code1, object_code2)
)