Beziehungslisten

Das Muster der Beziehungsliste ist ein gängiges relationales Muster, bei dem eine Tabelle einen Fremdschlüsselverweis auf sich selbst enthält, mit anderen Worten, es ist eine selbstbezügliche Beziehung. Dies ist der gebräuchlichste Weg, hierarchische Daten in flachen Tabellen darzustellen. Andere Methoden umfassen verschachtelte Mengen (manchmal auch als „modifizierter Präfix“ bezeichnet) sowie materialisierte Pfade. Trotz der Attraktivität des modifizierten Präfixes, wenn es um seine Flüssigkeit innerhalb von SQL-Abfragen geht, ist das Beziehungslistenmodell wahrscheinlich das am besten geeignete Muster für die überwiegende Mehrheit der hierarchischen Speicheranforderungen, aus Gründen der Nebenläufigkeit, reduzierten Komplexität und weil der modifizierte Präfix wenig Vorteil gegenüber einer Anwendung bietet, die vollständige Teilbäume in den Anwendungsbereich laden kann.

Siehe auch

Dieser Abschnitt beschreibt die Single-Table-Version einer selbstbezüglichen Beziehung. Für eine selbstbezügliche Beziehung, die eine zweite Tabelle als Assoziationstabelle verwendet, siehe den Abschnitt Selbstbezügliche Many-to-Many-Beziehung.

In diesem Beispiel arbeiten wir mit einer einzigen abgebildeten Klasse namens Node, die eine Baumstruktur repräsentiert

class Node(Base):
    __tablename__ = "node"
    id = mapped_column(Integer, primary_key=True)
    parent_id = mapped_column(Integer, ForeignKey("node.id"))
    data = mapped_column(String(50))
    children = relationship("Node")

Mit dieser Struktur ein Graph wie der folgende

root --+---> child1
       +---> child2 --+--> subchild1
       |              +--> subchild2
       +---> child3

Würde mit Daten wie diesen dargestellt werden

id       parent_id     data
---      -------       ----
1        NULL          root
2        1             child1
3        1             child2
4        3             subchild1
5        3             subchild2
6        1             child3

Die relationship()-Konfiguration funktioniert hier genauso wie eine „normale“ One-to-Many-Beziehung, mit der Ausnahme, dass die „Richtung“, d. h. ob die Beziehung One-to-Many oder Many-to-One ist, standardmäßig als One-to-Many angenommen wird. Um die Beziehung als Many-to-One zu etablieren, wird eine zusätzliche Direktive namens relationship.remote_side hinzugefügt, die eine Column oder eine Sammlung von Column-Objekten ist, die angeben, welche als „entfernt“ betrachtet werden sollen.

class Node(Base):
    __tablename__ = "node"
    id = mapped_column(Integer, primary_key=True)
    parent_id = mapped_column(Integer, ForeignKey("node.id"))
    data = mapped_column(String(50))
    parent = relationship("Node", remote_side=[id])

Wobei oben die Spalte id als relationship.remote_side der parent relationship() angewendet wird, wodurch parent_id als „lokale“ Seite etabliert wird und die Beziehung dann wie eine Many-to-One funktioniert.

Wie immer können beide Richtungen mit zwei relationship()-Konstrukten, die durch relationship.back_populates verbunden sind, zu einer bidirektionalen Beziehung kombiniert werden.

class Node(Base):
    __tablename__ = "node"
    id = mapped_column(Integer, primary_key=True)
    parent_id = mapped_column(Integer, ForeignKey("node.id"))
    data = mapped_column(String(50))
    children = relationship("Node", back_populates="parent")
    parent = relationship("Node", back_populates="children", remote_side=[id])

Siehe auch

Beziehungsliste – funktionierendes Beispiel, aktualisiert für SQLAlchemy 2.0

Zusammengesetzte Beziehungslisten

Eine Unterkategorie der Beziehungslistenbeziehung ist der seltene Fall, in dem eine bestimmte Spalte sowohl auf der „lokalen“ als auch auf der „entfernten“ Seite der Join-Bedingung vorhanden ist. Ein Beispiel ist die unten stehende Klasse Folder; bei Verwendung eines zusammengesetzten Primärschlüssels verweist die Spalte account_id auf sich selbst, um Unterordner anzuzeigen, die sich im selben Konto wie der Elternordner befinden; während folder_id auf einen bestimmten Ordner innerhalb dieses Kontos verweist.

class Folder(Base):
    __tablename__ = "folder"
    __table_args__ = (
        ForeignKeyConstraint(
            ["account_id", "parent_id"], ["folder.account_id", "folder.folder_id"]
        ),
    )

    account_id = mapped_column(Integer, primary_key=True)
    folder_id = mapped_column(Integer, primary_key=True)
    parent_id = mapped_column(Integer)
    name = mapped_column(String)

    parent_folder = relationship(
        "Folder", back_populates="child_folders", remote_side=[account_id, folder_id]
    )

    child_folders = relationship("Folder", back_populates="parent_folder")

Oben übergeben wir account_id in die Liste relationship.remote_side. relationship() erkennt, dass die Spalte account_id hier auf beiden Seiten vorhanden ist und ordnet die „entfernte“ Spalte zusammen mit der Spalte folder_id zu, die sie als eindeutig auf der „entfernten“ Seite vorhanden erkennt.

Selbstbezügliche Abfragestrategien

Das Abfragen selbstbezüglicher Strukturen funktioniert wie jede andere Abfrage

# get all nodes named 'child2'
session.scalars(select(Node).where(Node.data == "child2"))

Besondere Vorsicht ist jedoch geboten, wenn versucht wird, entlang des Fremdschlüssels von einer Ebene des Baumes zur nächsten zu joinen. In SQL erfordert ein Join von einer Tabelle zu sich selbst, dass mindestens eine Seite des Ausdrucks „aliased“ (mit einem Alias versehen) wird, damit er eindeutig referenziert werden kann.

Wie im ORM-Tutorial unter ORM-Aliase auswählen erwähnt, wird das Konstrukt aliased() normalerweise verwendet, um einen „Alias“ einer ORM-Entität bereitzustellen. Ein Join von Node zu sich selbst unter Verwendung dieser Technik sieht so aus:

from sqlalchemy.orm import aliased

nodealias = aliased(Node)
session.scalars(
    select(Node)
    .where(Node.data == "subchild1")
    .join(Node.parent.of_type(nodealias))
    .where(nodealias.data == "child2")
).all()
SELECT node.id AS node_id,
        node.parent_id AS node_parent_id,
        node.data AS node_data
FROM node JOIN node AS node_1
    ON node.parent_id = node_1.id
WHERE node.data = ?
    AND node_1.data = ?
['subchild1', 'child2']

Konfiguration des selbstbezüglichen Eager-Loadings

Eager Loading von Beziehungen erfolgt durch Joins oder Outer Joins von der Eltern- zur Kindertabelle während einer normalen Abfrageoperation, sodass die Eltern und ihre unmittelbare Kindersammlung oder Referenz aus einer einzigen SQL-Anweisung oder einer zweiten Anweisung für alle unmittelbaren Kindersammlungen abgerufen werden können. SQLAlchemy's Joined und Subquery Eager Loading verwenden in allen Fällen Aliase für Tabellen, wenn sie zu verwandten Elementen joinen, und sind daher mit selbstbezüglichem Joining kompatibel. Um Eager Loading jedoch mit einer selbstbezüglichen Beziehung zu verwenden, muss SQLAlchemy mitgeteilt werden, wie viele Ebenen tief es joinen und/oder abfragen soll; andernfalls findet das Eager Loading überhaupt nicht statt. Diese Tiefeneinstellung wird über relationships.join_depth konfiguriert.

class Node(Base):
    __tablename__ = "node"
    id = mapped_column(Integer, primary_key=True)
    parent_id = mapped_column(Integer, ForeignKey("node.id"))
    data = mapped_column(String(50))
    children = relationship("Node", lazy="joined", join_depth=2)


session.scalars(select(Node)).all()
SELECT node_1.id AS node_1_id,
        node_1.parent_id AS node_1_parent_id,
        node_1.data AS node_1_data,
        node_2.id AS node_2_id,
        node_2.parent_id AS node_2_parent_id,
        node_2.data AS node_2_data,
        node.id AS node_id,
        node.parent_id AS node_parent_id,
        node.data AS node_data
FROM node
    LEFT OUTER JOIN node AS node_2
        ON node.id = node_2.parent_id
    LEFT OUTER JOIN node AS node_1
        ON node_2.id = node_1.parent_id
[]