Отношения в списке смежности

Шаблон список примыканий - это распространенный реляционный шаблон, при котором таблица содержит внешнюю ключевую ссылку на саму себя, другими словами, это самореферентное отношение. Это наиболее распространенный способ представления иерархических данных в плоских таблицах. Другие методы включают вложенные множества, иногда называемые «модифицированным предзаказом», а также материализованный путь. Несмотря на привлекательность, которую имеет модифицированный порядок, если оценивать его с точки зрения легкости выполнения запросов SQL, модель списка смежности, вероятно, является наиболее подходящей моделью для большинства потребностей иерархического хранения данных по причинам параллелизма, снижения сложности и того, что модифицированный порядок имеет мало преимуществ перед приложением, которое может полностью загрузить поддеревья в пространство приложения.

См.также

В этом разделе описывается однотабличная версия самореферентного отношения. О самореферентных отношениях, использующих вторую таблицу в качестве таблицы ассоциации, см. раздел Отношения «многие-ко-многим» с самореференцией.

В этом примере мы будем работать с одним сопоставленным классом Node, представляющим древовидную структуру:

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")

При такой структуре можно построить граф, подобный следующему:

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

Может быть представлен такими данными, как:

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

Конфигурация relationship() здесь работает так же, как и «обычные» отношения один-ко-многим, за исключением того, что «направление», т.е. является ли отношение один-ко-многим или многие-к-одному, по умолчанию принимается равным один-ко-многим. Для установления отношения «многие-к-одному» добавляется дополнительная директива relationship.remote_side, которая представляет собой Column или коллекцию Column объектов, указывающих на те, которые следует считать «удаленными»:

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])

Там, где выше, столбец id применяется как relationship.remote_side из parent relationship(), таким образом устанавливая parent_id как «локальную» сторону, и отношения тогда ведут себя как многие-к-одному.

Как всегда, оба направления могут быть объединены в двунаправленную связь с помощью двух конструкций relationship(), связанных relationship.back_populates:

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])

См.также

Список примыканий - рабочий пример, обновленный для SQLAlchemy 2.0

Составные списки смежности

Подкатегория отношений списка смежности - это редкий случай, когда определенный столбец присутствует как на «локальной», так и на «удаленной» стороне условия соединения. Примером может служить приведенный ниже класс Folder; используя составной первичный ключ, столбец account_id ссылается сам на себя, чтобы указать подпапки, которые находятся в той же учетной записи, что и родительская; в то время как folder_id ссылается на конкретную папку в этой учетной записи:

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")

Выше мы передаем account_id в список relationship.remote_side. relationship() распознает, что столбец account_id здесь находится с обеих сторон, и выравнивает «удаленный» столбец вместе со столбцом folder_id, который он распознает как уникально присутствующий на «удаленной» стороне.

Стратегии самореференциальных запросов

Запрос к самореферентным структурам работает как любой другой запрос:

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

Однако при попытке соединения по внешнему ключу с одного уровня дерева на другой требуется дополнительная осторожность. В SQL соединение таблицы с самой собой требует, чтобы хотя бы одна сторона выражения была «алиасирована», чтобы на нее можно было однозначно ссылаться.

Вспомните из Выбор псевдонимов ORM в учебнике по ORM, что конструкция aliased() обычно используется для предоставления «псевдонима» сущности ORM. Присоединение от Node к самому себе с помощью этой техники выглядит следующим образом:

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()
{execsql}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']

Настройка самовоспроизводящейся ускоренной загрузки

Ускоренная загрузка отношений происходит с помощью объединений или внешних объединений от родительской к дочерней таблице во время обычной операции запроса, так что родительская и ее ближайшая дочерняя коллекция или ссылка могут быть заполнены из одного SQL-запроса, или второго запроса для всех ближайших дочерних коллекций. В SQLAlchemy при соединении и нетерпеливой загрузке подзапросов во всех случаях при соединении со связанными элементами используются псевдослучайные таблицы, поэтому они совместимы с самореферентным соединением. Однако, чтобы использовать ускоренную загрузку с самореферентными отношениями, SQLAlchemy необходимо указать, на сколько уровней вглубь он должен присоединиться и/или запросить; в противном случае ускоренная загрузка вообще не произойдет. Эта настройка глубины задается через relationships.join_depth:

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()
{execsql}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
[]
Основные модели взаимоотношений
Настройка способа присоединения отношений
Вернуться на верх