Настройка способа присоединения отношений¶

Работа с несколькими путями присоединения¶

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

Рассмотрим класс Customer, который содержит два внешних ключа к классу Address:

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address")
    shipping_address = relationship("Address")


class Address(Base):
    __tablename__ = "address"
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

Приведенное выше отображение, когда мы попытаемся его использовать, выдаст ошибку:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship
Customer.billing_address - there are multiple foreign key
paths linking the tables.  Specify the 'foreign_keys' argument,
providing a list of those columns which should be
counted as containing a foreign key reference to the parent table.

Приведенное выше сообщение довольно длинное. Существует множество потенциальных сообщений, которые может вернуть relationship(), которые были тщательно подобраны для обнаружения различных распространенных конфигурационных проблем; большинство из них предложит дополнительную конфигурацию, необходимую для разрешения двусмысленности или другой недостающей информации.

В этом случае сообщение хочет, чтобы мы квалифицировали каждый relationship(), указывая для каждого из них, какой столбец внешнего ключа должен быть рассмотрен, и соответствующая форма выглядит следующим образом:

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

Выше мы указали аргумент foreign_keys, который представляет собой Column или список объектов Column, указывающих на столбцы, которые следует считать «иностранными», или, другими словами, столбцы, содержащие значение, ссылающееся на родительскую таблицу. При загрузке отношения Customer.billing_address из объекта Customer будет использоваться значение, присутствующее в billing_address_id, чтобы определить строку в Address, которую нужно загрузить; аналогично, shipping_address_id используется для отношения shipping_address. Связь этих двух столбцов также играет роль при сохранении; только что созданный первичный ключ только что вставленного объекта Address будет скопирован в соответствующий столбец внешнего ключа связанного объекта Customer во время промывки.

При указании foreign_keys с помощью Declarative мы также можем использовать строковые имена для указания, однако важно, чтобы при использовании списка список был частью строки:

billing_address = relationship("Address", foreign_keys="[Customer.billing_address_id]")

В данном конкретном примере список не нужен в любом случае, так как есть только один Column, который нам нужен:

billing_address = relationship("Address", foreign_keys="Customer.billing_address_id")

Указание альтернативных условий присоединения¶

Поведение relationship() по умолчанию при построении объединения заключается в том, что оно приравнивает значение столбцов первичного ключа с одной стороны к значению столбцов, ссылающихся на иностранный ключ, с другой стороны. Мы можем изменить этот критерий на любой другой, используя аргумент relationship.primaryjoin, а также аргумент relationship.secondaryjoin в случае, когда используется «вторичная» таблица.

В примере ниже, используя класс User, а также класс Address, который хранит адрес улицы, мы создаем отношение boston_addresses, которое будет загружать только те объекты Address, которые указывают город «Бостон»:

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    boston_addresses = relationship(
        "Address",
        primaryjoin="and_(User.id==Address.user_id, " "Address.city=='Boston')",
    )


class Address(Base):
    __tablename__ = "address"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("user.id"))

    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

В этом строковом SQL-выражении мы использовали конструкцию соединения and_(), чтобы установить два разных предиката для условия объединения - объединение столбцов User.id и Address.user_id друг с другом, а также ограничение строк в Address только city='Boston'. При использовании Declarative рудиментарные функции SQL, такие как and_(), автоматически доступны в оцениваемом пространстве имен строкового аргумента relationship().

Пользовательские критерии, которые мы используем в relationship.primaryjoin, обычно важны только тогда, когда SQLAlchemy создает SQL для загрузки или представления этого отношения. То есть, он используется в SQL-запросе, который выдается для выполнения ленивой загрузки каждого атрибута, или когда соединение создается во время запроса, например, с помощью Query.join(), или с помощью стилей загрузки «объединенный» или «подзапрос». При работе с объектами в памяти мы можем поместить любой объект Address в коллекцию boston_addresses, независимо от значения атрибута .city. Объекты будут присутствовать в коллекции до тех пор, пока не истечет срок действия атрибута и не будет произведена повторная загрузка из базы данных, где применяется критерий. Когда происходит flush, объекты внутри boston_addresses будут выгружены безусловно, присваивая значение столбца первичного ключа user.id столбцу иностранного ключа address.user_id для каждой строки. Критерий city здесь не имеет никакого значения, так как процесс промывки заботится только о синхронизации значений первичного ключа со значениями внешнего ключа.

Создание пользовательских иностранных условий¶

Другим элементом первичного условия присоединения является способ определения столбцов, считающихся «иностранными». Обычно некоторое подмножество объектов Column будет указывать на ForeignKey или иным образом являться частью ForeignKeyConstraint, имеющего отношение к условию присоединения. relationship() смотрит на этот статус внешнего ключа, когда решает, как он должен загружать и сохранять данные для этого отношения. Однако аргумент relationship.primaryjoin можно использовать для создания условия присоединения, которое не включает никаких внешних ключей уровня схемы. Мы можем объединить relationship.primaryjoin вместе с relationship.foreign_keys и relationship.remote_side в явном виде, чтобы создать такое соединение.

Ниже класс HostEntry соединяется сам с собой, приравнивая строковый столбец content к столбцу ip_address, который является типом PostgreSQL под названием INET. Нам нужно использовать cast(), чтобы привести одну сторону соединения к типу другой:

from sqlalchemy import cast, String, Column, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import INET

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class HostEntry(Base):
    __tablename__ = "host_entry"

    id = Column(Integer, primary_key=True)
    ip_address = Column(INET)
    content = Column(String(50))

    # relationship() using explicit foreign_keys, remote_side
    parent_host = relationship(
        "HostEntry",
        primaryjoin=ip_address == cast(content, INET),
        foreign_keys=content,
        remote_side=ip_address,
    )

Вышеуказанное отношение создаст соединение вида:

SELECT host_entry.id, host_entry.ip_address, host_entry.content
FROM host_entry JOIN host_entry AS host_entry_1
ON host_entry_1.ip_address = CAST(host_entry.content AS INET)

Альтернативным синтаксисом по сравнению с приведенным выше является использование foreign() и remote() annotations, встроенных в выражение relationship.primaryjoin. Этот синтаксис представляет аннотации, которые relationship() обычно сам применяет к условию присоединения, учитывая аргументы relationship.foreign_keys и relationship.remote_side. Эти функции могут быть более лаконичными, когда присутствует явное условие присоединения, и дополнительно служат для маркировки именно того столбца, который является «иностранным» или «удаленным», независимо от того, указан ли этот столбец несколько раз или в сложных выражениях SQL:

from sqlalchemy.orm import foreign, remote


class HostEntry(Base):
    __tablename__ = "host_entry"

    id = Column(Integer, primary_key=True)
    ip_address = Column(INET)
    content = Column(String(50))

    # relationship() using explicit foreign() and remote() annotations
    # in lieu of separate arguments
    parent_host = relationship(
        "HostEntry",
        primaryjoin=remote(ip_address) == cast(foreign(content), INET),
    )

Использование пользовательских операторов в условиях присоединения¶

Другим вариантом использования отношений является применение пользовательских операторов, таких как оператор PostgreSQL «is contained within» << при объединении с такими типами, как INET и CIDR. Для пользовательских булевых операторов мы используем функцию Operators.bool_op():

inet_column.bool_op("<<")(cidr_column)

Сравнение, подобное приведенному выше, можно использовать непосредственно с relationship.primaryjoin при построении relationship():

class IPA(Base):
    __tablename__ = "ip_address"

    id = Column(Integer, primary_key=True)
    v4address = Column(INET)

    network = relationship(
        "Network",
        primaryjoin="IPA.v4address.bool_op('<<')" "(foreign(Network.v4representation))",
        viewonly=True,
    )


class Network(Base):
    __tablename__ = "network"

    id = Column(Integer, primary_key=True)
    v4representation = Column(CIDR)

Выше, запрос типа:

session.query(IPA).join(IPA.network)

Будет отображаться как:

SELECT ip_address.id AS ip_address_id, ip_address.v4address AS ip_address_v4address
FROM ip_address JOIN network ON ip_address.v4address << network.v4representation

Пользовательские операторы на основе функций SQL¶

Вариант использования Operators.op.is_comparison - это когда мы используем не оператор, а SQL-функцию. Типичным примером такого использования являются функции PostgreSQL PostGIS, однако может применяться любая SQL-функция любой базы данных, которая разрешается в двоичное условие. Для этого случая метод FunctionElement.as_comparison() может модифицировать любую SQL-функцию, например, вызываемую из пространства имен func, чтобы указать ORM, что функция производит сравнение двух выражений. Приведенный ниже пример иллюстрирует это на примере библиотеки Geoalchemy2:

from geoalchemy2 import Geometry
from sqlalchemy import Column, Integer, func
from sqlalchemy.orm import relationship, foreign


class Polygon(Base):
    __tablename__ = "polygon"
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry("POLYGON", srid=4326))
    points = relationship(
        "Point",
        primaryjoin="func.ST_Contains(foreign(Polygon.geom), Point.geom).as_comparison(1, 2)",
        viewonly=True,
    )


class Point(Base):
    __tablename__ = "point"
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry("POINT", srid=4326))

Выше, FunctionElement.as_comparison() указывает, что SQL-функция func.ST_Contains() сравнивает выражения Polygon.geom и Point.geom. Аннотация foreign() дополнительно отмечает, какой столбец играет роль «внешнего ключа» в данном конкретном отношении.

Перекрывающиеся внешние ключи¶

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

Рассмотрим (допустимо сложное) отображение, такое как объект Magazine, на который ссылаются объект Writer и объект Article, используя составную схему первичного ключа, которая включает magazine_id для обоих; затем, чтобы заставить Article ссылаться на Writer также, Article.magazine_id участвует в двух отдельных отношениях; Article.magazine и Article.writer:

class Magazine(Base):
    __tablename__ = "magazine"

    id = Column(Integer, primary_key=True)


class Article(Base):
    __tablename__ = "article"

    article_id = Column(Integer)
    magazine_id = Column(ForeignKey("magazine.id"))
    writer_id = Column()

    magazine = relationship("Magazine")
    writer = relationship("Writer")

    __table_args__ = (
        PrimaryKeyConstraint("article_id", "magazine_id"),
        ForeignKeyConstraint(
            ["writer_id", "magazine_id"], ["writer.id", "writer.magazine_id"]
        ),
    )


class Writer(Base):
    __tablename__ = "writer"

    id = Column(Integer, primary_key=True)
    magazine_id = Column(ForeignKey("magazine.id"), primary_key=True)
    magazine = relationship("Magazine")

Когда вышеуказанное отображение настроено, мы увидим следующее предупреждение:

SAWarning: relationship 'Article.writer' will copy column
writer.magazine_id to column article.magazine_id,
which conflicts with relationship(s): 'Article.magazine'
(copies magazine.id to article.magazine_id). Consider applying
viewonly=True to read-only relationships, or provide a primaryjoin
condition marking writable columns with the foreign() annotation.

Это связано с тем, что Article.magazine_id является предметом двух различных ограничений внешнего ключа; он ссылается на Magazine.id непосредственно как на исходный столбец, но также ссылается на Writer.magazine_id как на исходный столбец в контексте составного ключа к Writer. Если мы свяжем Article с определенным Magazine, но затем свяжем Article с Writer, который связан с другим Magazine, ORM недетерминированно перезапишет Article.magazine_id, молча изменив журнал, на который мы ссылаемся; он также может попытаться поместить NULL в этот столбец, если мы де-ассоциируем Writer с Article. Предупреждение дает нам знать об этом.

Чтобы решить эту проблему, нам нужно разбить поведение Article на все три следующие функции:

1. Article в первую очередь записывает в Article.magazine_id на основе данных, сохраненных только в отношении Article.magazine, то есть значения, скопированного из Magazine.id.

2. Article может записывать в Article.writer_id от имени данных, хранящихся в отношениях Article.writer, но только столбец Writer.id; столбец Writer.magazine_id не должен записываться в Article.magazine_id, поскольку в конечном итоге он берется из Magazine.id.

3. Article учитывает Article.magazine_id при загрузке Article.writer, даже если он не пишет в него от имени этого отношения.

Чтобы получить только #1 и #2, мы можем указать только Article.writer_id в качестве «внешних ключей» для Article.writer:

class Article(Base):
    # ...

    writer = relationship("Writer", foreign_keys="Article.writer_id")

Однако это приводит к тому, что Article.writer не учитывает Article.magazine_id при запросе к Writer:

SELECT article.article_id AS article_article_id,
    article.magazine_id AS article_magazine_id,
    article.writer_id AS article_writer_id
FROM article
JOIN writer ON writer.id = article.writer_id

Поэтому, чтобы получить все из #1, #2 и #3, мы выражаем условие присоединения, а также то, какие столбцы должны быть записаны, сочетая relationship.primaryjoin полностью, вместе с аргументом relationship.foreign_keys, или более кратко, аннотируя foreign():

class Article(Base):
    # ...

    writer = relationship(
        "Writer",
        primaryjoin="and_(Writer.id == foreign(Article.writer_id), "
        "Writer.magazine_id == Article.magazine_id)",
    )

Нереляционные сравнения / Материализованный путь¶

Использование пользовательских выражений означает, что мы можем создавать нестандартные условия объединения, которые не подчиняются обычной модели первичного/внешнего ключа. Одним из таких примеров является шаблон материализованного пути, в котором мы сравниваем строки на предмет пересекающихся маркеров пути, чтобы создать древовидную структуру.

Благодаря тщательному использованию foreign() и remote(), мы можем построить отношения, которые эффективно создают рудиментарную систему материализованных путей. По сути, когда foreign() и remote() находятся на одной стороне выражения сравнения, отношение считается «один ко многим»; когда они находятся на разных сторонах, отношение считается «многие к одному». Для сравнения, которое мы будем использовать здесь, мы будем иметь дело с коллекциями, поэтому мы сохраним конфигурацию «один ко многим»:

class Element(Base):
    __tablename__ = "element"

    path = Column(String, primary_key=True)

    descendants = relationship(
        "Element",
        primaryjoin=remote(foreign(path)).like(path.concat("/%")),
        viewonly=True,
        order_by=path,
    )

Выше, если дан объект Element с атрибутом path "/foo/bar2", мы ищем загрузку Element.descendants, чтобы она выглядела так:

SELECT element.path AS element_path
FROM element
WHERE element.path LIKE ('/foo/bar2' || '/%') ORDER BY element.path

Самореферентные отношения «многие ко многим¶

Отношения «многие ко многим» могут быть настроены с помощью одного или обоих аргументов relationship.primaryjoin и relationship.secondaryjoin - последний имеет значение для отношения, которое определяет ссылку «многие ко многим» с помощью аргумента relationship.secondary. Распространенная ситуация, в которой используются relationship.primaryjoin и relationship.secondaryjoin - это установление отношения «многие-ко-многим» от класса к самому себе, как показано ниже:

from sqlalchemy import Integer, ForeignKey, String, Column, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

node_to_node = Table(
    "node_to_node",
    Base.metadata,
    Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
    Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True),
)


class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    label = Column(String)
    right_nodes = relationship(
        "Node",
        secondary=node_to_node,
        primaryjoin=id == node_to_node.c.left_node_id,
        secondaryjoin=id == node_to_node.c.right_node_id,
        backref="left_nodes",
    )

Там, где указано выше, SQLAlchemy не может автоматически определить, какие столбцы должны соединяться с какими для отношений right_nodes и left_nodes. Аргументы relationship.primaryjoin и relationship.secondaryjoin устанавливают, как мы хотим присоединиться к таблице ассоциаций. В приведенной выше декларативной форме, поскольку мы объявляем эти условия в блоке Python, соответствующем классу Node, переменная id доступна непосредственно как объект Column, к которому мы хотим присоединиться.

В качестве альтернативы мы можем определить аргументы relationship.primaryjoin и relationship.secondaryjoin с помощью строк, что подходит в случае, если в нашей конфигурации еще не доступен объект столбца Node.id или, возможно, еще не доступна таблица node_to_node. При ссылке на простой объект Table в декларативной строке мы используем строковое имя таблицы в том виде, в котором оно присутствует в MetaData:

class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    label = Column(String)
    right_nodes = relationship(
        "Node",
        secondary="node_to_node",
        primaryjoin="Node.id==node_to_node.c.left_node_id",
        secondaryjoin="Node.id==node_to_node.c.right_node_id",
        backref="left_nodes",
    )

Классическая ситуация с отображением здесь аналогична, когда node_to_node может быть присоединено к node.c.id:

from sqlalchemy import Integer, ForeignKey, String, Column, Table, MetaData
from sqlalchemy.orm import relationship, registry

metadata_obj = MetaData()
mapper_registry = registry()

node_to_node = Table(
    "node_to_node",
    metadata_obj,
    Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
    Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True),
)

node = Table(
    "node",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("label", String),
)


class Node(object):
    pass


mapper_registry.map_imperatively(
    Node,
    node,
    properties={
        "right_nodes": relationship(
            Node,
            secondary=node_to_node,
            primaryjoin=node.c.id == node_to_node.c.left_node_id,
            secondaryjoin=node.c.id == node_to_node.c.right_node_id,
            backref="left_nodes",
        )
    },
)

Обратите внимание, что в обоих примерах ключевое слово relationship.backref задает обратную ссылку left_nodes - когда relationship() создает второе отношение в обратном направлении, он достаточно умен, чтобы поменять местами аргументы relationship.primaryjoin и relationship.secondaryjoin.

Композитные «вторичные» соединения¶

Иногда, когда необходимо построить relationship() между двумя таблицами, для их соединения требуется больше, чем просто две или три таблицы. Это та область relationship(), где необходимо расширить границы возможного, и часто окончательное решение многих из этих экзотических случаев использования необходимо выработать в списке рассылки SQLAlchemy.

В более новых версиях SQLAlchemy параметр relationship.secondary может быть использован в некоторых из этих случаев для обеспечения составной цели, состоящей из нескольких таблиц. Ниже приведен пример такого условия соединения (для работы требуется версия не ниже 0.9.2):

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey("b.id"))

    d = relationship(
        "D",
        secondary="join(B, D, B.d_id == D.id)." "join(C, C.d_id == D.id)",
        primaryjoin="and_(A.b_id == B.id, A.id == C.a_id)",
        secondaryjoin="D.id == B.d_id",
        uselist=False,
        viewonly=True,
    )


class B(Base):
    __tablename__ = "b"

    id = Column(Integer, primary_key=True)
    d_id = Column(ForeignKey("d.id"))


class C(Base):
    __tablename__ = "c"

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    d_id = Column(ForeignKey("d.id"))


class D(Base):
    __tablename__ = "d"

    id = Column(Integer, primary_key=True)

В приведенном выше примере мы предоставляем все три из relationship.secondary, relationship.primaryjoin и relationship.secondaryjoin, в декларативном стиле ссылаясь на именованные таблицы a, b, c, d непосредственно. Запрос от A к D имеет вид:

sess.query(A).join(A.d).all()

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a JOIN (
    b AS b_1 JOIN d AS d_1 ON b_1.d_id = d_1.id
        JOIN c AS c_1 ON c_1.d_id = d_1.id)
    ON a.b_id = b_1.id AND a.id = c_1.a_id JOIN d ON d.id = b_1.d_id

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

Отношения с чужим классом¶

В предыдущем разделе мы проиллюстрировали технику, в которой мы использовали relationship.secondary для размещения дополнительных таблиц в условии соединения. Существует один сложный случай присоединения, когда даже этой техники недостаточно; когда мы пытаемся присоединиться от A к B, используя любое количество C, D и т.д. между ними, однако существуют также условия присоединения между A и B прямо. В этом случае соединение от A к B может быть трудно выразить только сложным условием relationship.primaryjoin, поскольку промежуточные таблицы могут нуждаться в специальной обработке, и оно также не может быть выражено с помощью объекта relationship.secondary, поскольку шаблон A->secondary->B не поддерживает никаких ссылок между A и B непосредственно. Когда возникает этот экстремальный случай, мы можем прибегнуть к созданию второго отображения в качестве цели для связи. Здесь мы используем AliasedClass, чтобы создать отображение на класс, который включает все дополнительные таблицы, необходимые нам для этого соединения. Чтобы создать это отображение в качестве «альтернативного» отображения для нашего класса, мы используем функцию aliased() для создания новой конструкции, а затем используем relationship() против объекта, как если бы это был обычный отображенный класс.

Ниже показан relationship() с простым соединением от A к B, однако условие первичного соединения дополнено двумя дополнительными сущностями C и D, которые также должны иметь строки, совпадающие со строками в A и B одновременно:

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey("b.id"))


class B(Base):
    __tablename__ = "b"

    id = Column(Integer, primary_key=True)


class C(Base):
    __tablename__ = "c"

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))

    some_c_value = Column(String)


class D(Base):
    __tablename__ = "d"

    id = Column(Integer, primary_key=True)
    c_id = Column(ForeignKey("c.id"))
    b_id = Column(ForeignKey("b.id"))

    some_d_value = Column(String)


# 1. set up the join() as a variable, so we can refer
# to it in the mapping multiple times.
j = join(B, D, D.b_id == B.id).join(C, C.id == D.c_id)

# 2. Create an AliasedClass to B
B_viacd = aliased(B, j, flat=True)

A.b = relationship(B_viacd, primaryjoin=A.b_id == j.c.b_id)

С приведенным выше отображением простое соединение выглядит следующим образом:

sess.query(A).join(A.b).all()

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a JOIN (b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) ON a.b_id = b.id

subq = select(B).join(D, D.b_id == B.id).join(C, C.id == D.c_id).subquery()

B_viacd_subquery = aliased(B, subq)

A.b = relationship(B_viacd_subquery, primaryjoin=A.b_id == subq.c.id)

sess.query(A).join(A.b).all()

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a JOIN (SELECT b.id AS id, b.some_b_column AS some_b_column
FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) AS anon_1 ON a.b_id = anon_1.id

(
    sess.query(A)
    .join(A.b)
    .filter(B_viacd_subquery.some_b_column == "some b")
    .order_by(B_viacd_subquery.id)
).all()

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a JOIN (SELECT b.id AS id, b.some_b_column AS some_b_column
FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) AS anon_1 ON a.b_id = anon_1.id
WHERE anon_1.some_b_column = ? ORDER BY anon_1.id

Отношения, ограниченные строками, с помощью оконных функций¶

Другим интересным случаем использования отношений к объектам AliasedClass являются ситуации, когда отношение должно присоединяться к специализированному SELECT любой формы. Один из сценариев - когда требуется использование оконной функции, например, для ограничения количества строк, которые должны быть возвращены для отношения. Пример ниже иллюстрирует непервичное отношение отображения, которое будет загружать первые десять элементов для каждой коллекции:

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)


class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))


partition = select(
    B, func.row_number().over(order_by=B.id, partition_by=B.a_id).label("index")
).alias()

partitioned_b = aliased(B, partition)

A.partitioned_bs = relationship(
    partitioned_b, primaryjoin=and_(partitioned_b.a_id == A.id, partition.c.index < 10)
)

Мы можем использовать приведенное выше отношение partitioned_bs с большинством стратегий загрузчика, таких как selectinload():

for a1 in s.query(A).options(selectinload(A.partitioned_bs)):
    print(a1.partitioned_bs)  # <-- will be no more than ten objects

Где выше, запрос «selectinload» выглядит как:

SELECT
    a_1.id AS a_1_id, anon_1.id AS anon_1_id, anon_1.a_id AS anon_1_a_id,
    anon_1.data AS anon_1_data, anon_1.index AS anon_1_index
FROM a AS a_1
JOIN (
    SELECT b.id AS id, b.a_id AS a_id, b.data AS data,
    row_number() OVER (PARTITION BY b.a_id ORDER BY b.id) AS index
    FROM b) AS anon_1
ON anon_1.a_id = a_1.id AND anon_1.index < %(index_1)s
WHERE a_1.id IN ( ... primary key collection ...)
ORDER BY a_1.id

Выше, для каждого совпадающего первичного ключа в «a» мы получим первые десять «bs», упорядоченные по «b.id». Разбиение по «a_id» гарантирует, что каждый «номер строки» является локальным для родительского «a_id».

Такое отображение, как правило, также включает «простое» отношение от «A» к «B» для операций сохранения, а также когда требуется полный набор объектов «B» на «A».

Построение свойств с поддержкой запросов¶

Очень амбициозные пользовательские условия присоединения могут не быть непосредственно персистентными, а в некоторых случаях могут даже не загружаться корректно. Чтобы устранить персистентную часть уравнения, используйте флаг relationship.viewonly на relationship(), который устанавливает его как атрибут только для чтения (данные, записанные в коллекцию, будут игнорироваться при flush()). Однако в крайних случаях рассмотрите возможность использования обычного свойства Python в сочетании с Query следующим образом:

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)

    @property
    def addresses(self):
        return object_session(self).query(Address).with_parent(self).filter(...).all()

В других случаях дескриптор может быть создан для использования существующих в Python данных. Более общее обсуждение специальных атрибутов Python см. в разделе Использование дескрипторов и гибридов.