Search code examples
pythonsqlsqlalchemypolymorphismrelation

SQLAlchemy Polymorphic Many-to-many Relationship

Does SQLAlchemy provide a convenient pattern to associate multiple classes with another class using a single table? For example, making the classes “Post“ and “Video” taggable by using a polymorphic many-to-many relationship with a “Tag” class?

I am familiar with the implementation of many-to-many polymorphic relationships in the Laravel PHP framework. This implementation uses a type field in the joining table to distinguish between classes “Post” and “Video".

Example:

posts
    id - integer
    name - string

videos
    id - integer
    name - string

tags
    id - integer
    name - string

taggables
    tag_id - integer
    taggable_id - integer
    taggable_type - string

I couldn’t find anything similar for SQLAlchemy. To my understanding, Polymorphism in SQLalchemy is limited to patterns for class inheritance. Similarly, the association object pattern only seems to allow relationships between 2 classes.

Are polymorphic patterns like my example above recommended? Or is it generally better to define a many-to-many relationship with “Tag” for each class?

Solution

  • You can absolutely implement this with sqlalchemy.

    Please read Examples: Generic Associations section of the documentation for various options.

    The one you are looking for is shown in discriminator_on_association code snippet.