Creating Container Relationship In Declarative SQLAlchemy

My Python / SQLAlchemy application manages a set of nodes, all derived from a base class Node. I'm using SQLAlchemy's polymorphism features to manage the nodes in a SQLite3 table.

Solution 1:

You need an additional table for many-to-many relation:

nodes_list_nodes = Table(
    'nodes_list_nodes', metadata,
    Column('parent_id', None, ForeignKey('nodes_list.id'), nullable=False),
    Column('child_id', None, ForeignKey(Node.id), nullable=False),
    PrimaryKeyConstraint('parent_id', 'child_id'),
)

class ListNode(Node):
    __mapper_args__ = {'polymorphic_identity': 'list'}
    __tablename__ = 'nodes_list'
    id = Column(None, ForeignKey('nodes.id'), primary_key=True)
    items = relation(Node, secondary=nodes_list_nodes)

Update: below is an example for ordered list using association_proxy:

from sqlalchemy.orm.collections import InstrumentedList
from sqlalchemy.ext.associationproxy import association_proxy


class ListNodeAssociation(Base):
    __tablename__ = 'nodes_list_nodes'
    parent_id = Column(None, ForeignKey('nodes_list.id'), primary_key=True)
    child_id = Column(None, ForeignKey(Node.id), primary_key=True)
    order = Column(Integer, nullable=False, default=0)
    child = relation(Node)
    __table_args__ = (
        PrimaryKeyConstraint('parent_id', 'child_id'),
        {},
    )


class OrderedList(InstrumentedList):

    def append(self, item):
        if self:
            item.order = self[-1].order+1
        else:
            item.order = 1
        InstrumentedList.append(self, item)


class ListNode(Node):
    __mapper_args__ = {'polymorphic_identity': 'list'}
    __tablename__ = 'nodes_list'
    id = Column(None, ForeignKey('nodes.id'), primary_key=True)
    _items = relation(ListNodeAssociation,
                      order_by=ListNodeAssociation.order,
                      collection_class=OrderedList,
                      cascade='all, delete-orphan')
    items = association_proxy(
                '_items', 'child',
                creator=lambda item: ListNodeAssociation(child=item))