sqlalchemy - Mapping self-referential relationship as one to many (declarative form)

Question

Hi,

I want to map a Tag entity using declarative method with sqlachemy. A tag can have a parent (another Tag).

I have:

class Tag(Base):
    __tablename__ = 'tag'

    id = Column(Integer, primary_key=True)
    label = Column(String)

    def __init__(self, label, parentTag=None):
        self.label = label

how can add the "parent" relationship?

Thanks

Answer 1

parent = relation('Tag') — see http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html#configuring-relations.

Answer 2

You add a foreign key referencing the parent, and then create a relation that specifies the direction via remote side. This is documented under adjacency list relationships. For declarative you'd do something like this:

class Tag(Base):
    __tablename__ = 'tag'

    id = Column(Integer, primary_key=True)
    label = Column(String)
    parent_id = Column(Integer, ForeignKey('tag.id'))

    parent = relation('Tag', remote_side=[id])

If you want the reverse relation also, add backref="children" to the relation definition.