How can SqlAlchemy establish a relationship between tables that inherit from a common model?

edit

To establish a relationship between tables that inherit from a common model in SQLAlchemy, you can use the polymorphic_on and polymorphic_identity parameters in the model definitions.

Here is an example:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    # This is the polymorphic_column that will
    # determine the type of object in the database
    type = Column(String(50))

    # Define relationships
    pets = relationship('Pet', back_populates='owner', cascade='all, delete-orphan', single_parent=True)

    __mapper_args__ = {
        'polymorphic_on': type,
        'polymorphic_identity': 'person'
    }

class Pet(Base):
    __tablename__ = 'pet'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    owner_id = Column(Integer, ForeignKey('person.id'))

    # Define relationships
    owner = relationship('Person', back_populates='pets')

    __mapper_args__ = {
        'polymorphic_on': type,
        'polymorphic_identity': 'pet'
    }

class Dog(Pet):
    __mapper_args__ = {
        'polymorphic_identity': 'dog'
    }

class Cat(Pet):
    __mapper_args__ = {
        'polymorphic_identity': 'cat'
    }

In the above example, we have a base Person class and a Pet class that inherit from it. The type column in both classes specifies the type of object that is being stored in the database.

We also have two subclasses of Pet - Dog and Cat.

The __mapper_args__ attribute is used to specify the polymorphic_on parameter, which is set to the type column in both the Person and Pet classes. We also set the polymorphic_identity parameter to 'person' in the Person class, and 'pet', 'dog', and 'cat' in their respective classes.

With this setup, we can easily establish relationships between Person and Pet objects, and query them accordingly. For example, we can get a list of all pets owned by a particular person like this:

person = session.query(Person).filter_by(name='John').first()
pets = person.pets

And we can get a list of all pets (of any type) with their owners like this:

pets = session.query(Pet).options(joinedload('owner')).all()

In summary, to establish relationships between tables that inherit from a common model in SQLAlchemy, you can use the __mapper_args__ attribute with the polymorphic_on and polymorphic_identity parameters, and then define relationships between the parent and child classes as usual.