SQLAlchemy Combine Multiple Child Classes in one Relationship

Question

I am wondering if somebody can help me out on my SQLAlchemy problem of combining multiple child classes in one relationship.

I am working on a project management app where I have a task breakdown structure as follows:

Summary Task -> Task -> Subtask -> Located Task

Summary Tasks have the ability to reference another summary task forming a hierarchy. Meanwhile, tasks does not necessarily belong to a summary task.

My current solution is to reference them in separate relationships:

class SummaryTask(Base):
    __tablename__ = 'summary_task'

    id = Column(Integer, primary_key=True)
    schedule_id = Column(ForeignKey('schedule.id'), primary_key=True)
    parent_id = Column(Integer)
    parent_schedule_id = Column(Integer)
    additional_data = Column(Unicode(50))

    children = relationship('SummaryTask', backref=backref('parent', remote_side=[id, schedule_id]))
    tasks = relationship('Task', backref=backref('parent'))

class Task(Base):
    __tablename__ = 'task'
    __table_args__ = (ForeignKeyConstraint(['summary_task_id', 'schedule_id'], ['summary_task.id', 'summary_task.schedule_id']))

    id = Column(Integer, primary_key=True)
    schedule_id = Column(ForeignKey('schedule.id'), primary_key=True)
    summary_task_id = Column(Integer)
    
    summary_task = relationship('SumTask')

The code snippet above works for me, but I would like to combine summary tasks (children) and tasks in children, as both are indeed children of summary tasks.

class SummaryTask(Base):
    ...

    children = relationship('SummaryTask', 'Task' ...)

My interface will then be as follows from Pydantic:

class SummaryTaskInterface(BaseModel):
    id: int
    schedule_id: int
    parent_id: Optional[int] = None
    parent_schedule_id: Optional[int] = None
    additional_data: str

    children: List[Union['SummaryTaskInterface', 'TaskInterface']] = []

I hope someone can help my out here.

Kind regards

Answer 1

I ended up with this solution.

class SummaryTask(Base):
    __tablename__ = 'summary_task'

    id = Column(Integer, primary_key=True)
    schedule_id = Column(ForeignKey('schedule.id'), primary_key=True)
    parent_id = Column(Integer)
    parent_schedule_id = Column(Integer)
    additional_data = Column(Unicode(50))

    summary_tasks = relationship('SummaryTask', backref=backref('parent', remote_side=[id, schedule_id]))
    tasks = relationship('Task', backref=backref('parent'))

    @hybrid_property
    def children(self):
        return self.summary_tasks + self.tasks

With the already suggested interface.

class SummaryTaskInterface(BaseModel):
    id: int
    schedule_id: int
    parent_id: Optional[int] = None
    parent_schedule_id: Optional[int] = None
    additional_data: str

    children: List[Union['SummaryTaskInterface', 'TaskInterface']] = []