Отношения в списке смежности¶
Шаблон список примыканий - это распространенный реляционный шаблон, при котором таблица содержит внешнюю ключевую ссылку на саму себя, другими словами, это самореферентное отношение. Это наиболее распространенный способ представления иерархических данных в плоских таблицах. Другие методы включают вложенные множества, иногда называемые «модифицированным предзаказом», а также материализованный путь. Несмотря на привлекательность, которую имеет модифицированный порядок, если оценивать его с точки зрения легкости выполнения запросов SQL, модель списка смежности, вероятно, является наиболее подходящей моделью для большинства потребностей иерархического хранения данных по причинам параллелизма, снижения сложности и того, что модифицированный порядок имеет мало преимуществ перед приложением, которое может полностью загрузить поддеревья в пространство приложения.
См.также
В этом разделе описывается однотабличная версия самореферентного отношения. Для самореферентных отношений, использующих вторую таблицу в качестве таблицы ассоциации, см. раздел Самореферентные отношения «многие ко многим.
В этом примере мы будем работать с одним сопоставленным классом Node
, представляющим древовидную структуру:
class Node(Base):
__tablename__ = "node"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("node.id"))
data = 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 = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("node.id"))
data = Column(String(50))
parent = relationship("Node", remote_side=[id])
Там, где выше, столбец id
применяется как relationship.remote_side
из parent
relationship()
, таким образом, устанавливая parent_id
как «локальную» сторону, и отношения тогда ведут себя как многие-к-одному.
Как всегда, оба направления можно объединить в двунаправленную связь с помощью функции backref()
:
class Node(Base):
__tablename__ = "node"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("node.id"))
data = Column(String(50))
children = relationship("Node", backref=backref("parent", remote_side=[id]))
В комплект поставки SQLAlchemy входит несколько примеров, иллюстрирующих самореферентные стратегии; среди них Список примыканий и Постоянство XML.
Составные списки смежности¶
Подкатегория отношений списка смежности - это редкий случай, когда определенный столбец присутствует как на «локальной», так и на «удаленной» стороне условия соединения. Примером может служить приведенный ниже класс 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 = Column(Integer, primary_key=True)
folder_id = Column(Integer, primary_key=True)
parent_id = Column(Integer)
name = Column(String)
parent_folder = relationship(
"Folder", backref="child_folders", remote_side=[account_id, folder_id]
)
Выше мы передаем account_id
в список relationship.remote_side
. relationship()
распознает, что столбец account_id
здесь находится с обеих сторон, и выравнивает «удаленный» столбец вместе со столбцом folder_id
, который он распознает как уникально присутствующий на «удаленной» стороне.
Стратегии самореференциальных запросов¶
Запрос к самореферентным структурам работает как любой другой запрос:
# get all nodes named 'child2'
session.query(Node).filter(Node.data == "child2")
Однако при попытке соединения по внешнему ключу с одного уровня дерева на другой требуется дополнительная осторожность. В SQL соединение таблицы с самой собой требует, чтобы хотя бы одна сторона выражения была «алиасирована», чтобы на нее можно было однозначно ссылаться.
Вспомните из Выбор псевдонимов ORM в учебнике по ORM, что конструкция aliased()
обычно используется для предоставления «псевдонима» сущности ORM. Присоединение от Node
к самому себе с помощью этой техники выглядит следующим образом:
from sqlalchemy.orm import aliased
nodealias = aliased(Node)
session.query(Node).filter(Node.data == "subchild1").join(
Node.parent.of_type(nodealias)
).filter(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']
Пример использования aliased()
для соединения через произвольно длинную цепочку самореферентных узлов см. в Постоянство XML.
Настройка самовоспроизводящейся ускоренной загрузки¶
Ускоренная загрузка отношений происходит с помощью соединений или внешних соединений от родительской к дочерней таблице во время обычной операции запроса, так что родительская и ее ближайшая дочерняя коллекция или ссылка могут быть заполнены из одного SQL-запроса, или второго запроса для всех ближайших дочерних коллекций. В SQLAlchemy при соединении и нетерпеливой загрузке подзапросов во всех случаях при соединении со связанными элементами используются псевдослучайные таблицы, поэтому они совместимы с самореферентным соединением. Однако, чтобы использовать ускоренную загрузку с самореферентными отношениями, SQLAlchemy необходимо указать, на сколько уровней вглубь он должен присоединиться и/или запросить; в противном случае ускоренная загрузка вообще не произойдет. Эта настройка глубины задается через relationships.join_depth
:
class Node(Base):
__tablename__ = "node"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("node.id"))
data = Column(String(50))
children = relationship("Node", lazy="joined", join_depth=2)
session.query(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
[]