SqlAlchemy 2.0 中文文档(十五)(4)https://developer.aliyun.com/article/1562982
沿关系路径连接到子查询
在前一节中示例的子查询形式可以使用更具体的方式来表达,使用一个relationship()
绑定的属性,使用使用关系在别名目标之间进行连接中指示的形式之一。例如,要创建相同的连接,并确保连接沿着特定relationship()
进行,我们可以使用PropComparator.of_type()
方法,传递包含要连接的Subquery
对象的aliased()
构造:
>>> address_subq = aliased(Address, subq, name="address") >>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq)) >>> for row in session.execute(stmt): ... print(f"{row.User} {row.address}") SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address FROM user_account JOIN (SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address FROM address WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id [...] ('pat999@aol.com',) User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')
引用多个实体的子查询
包含跨越多个 ORM 实体的列的子查询可以同时应用于多个aliased()
构造,并在相同的Select
构造中按照每个实体分别处理。然而,生成的 SQL 仍将所有这些aliased()
构造视为相同的子查询,但是从 ORM / Python 的角度来看,可以使用适当的aliased()
构造来引用不同的返回值和对象属性。
例如,给定一个同时引用User
和Address
的子查询:
>>> user_address_subq = ( ... select(User.id, User.name, User.fullname, Address.id, Address.email_address) ... .join_from(User, Address) ... .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"])) ... .subquery() ... )
我们可以针对User
和Address
分别创建aliased()
构造,它们各自指向相同的对象:
>>> user_alias = aliased(User, user_address_subq, name="user") >>> address_alias = aliased(Address, user_address_subq, name="address")
从两个实体中进行选择的Select
构造将会渲染子查询一次,但在结果行上下文中可以同时返回User
和Address
类的对象:
>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy") >>> for row in session.execute(stmt):
设置连接中最左侧的 FROM 子句
在当前Select
状态的左侧与我们想要连接的内容不符合的情况下,可以使用Select.join_from()
方法:
>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1
Select.join_from()
方法接受两个或三个参数,可以是(, )
形式,也可以是(, , [])
形式:
>>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1
为了设置初始的 FROM 子句,以便之后可以使用 Select.join()
,可以使用 Select.select_from()
方法:
>>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1
提示
Select.select_from()
方法实际上并不决定 FROM 子句中表的顺序。如果语句还引用了指向不同顺序的现有表的 Join
结构,那么 Join
结构将优先。当我们使用 Select.join()
和 Select.join_from()
等方法时,这些方法最终创建了这样一个 Join
对象。因此,在这种情况下,我们可以看到 Select.select_from()
的内容被覆盖了:
>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = :name_1
在上面的例子中,我们看到 FROM 子句是 address JOIN user_account
,尽管我们首先声明了 select_from(User)
。由于 .join(Address.user)
方法调用,该语句最终等同于以下内容:
>>> from sqlalchemy.sql import join >>> >>> user_table = User.__table__ >>> address_table = Address.__table__ >>> >>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id) >>> stmt = ( ... select(address_table) ... .select_from(user_table) ... .select_from(j) ... .where(user_table.c.name == "sandy") ... ) >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = :name_1
上述的 Join
结构被添加为 Select.select_from()
列表中的另一个条目,它取代了先前的条目。 ### 简单的关系连接
考虑两个类 User
和 Address
之间的映射,其中关系 User.addresses
表示与每个 User
关联的 Address
对象的集合。Select.join()
的最常见用法是沿着这种关系创建 JOIN,使用 User.addresses
属性作为指示器指示应该如何进行连接:
>>> stmt = select(User).join(User.addresses)
在上面的例子中,对 User.addresses
使用 Select.join()
的调用将导致大致等效于以下 SQL:
>>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id
在上面的示例中,我们将User.addresses
称为传递给Select.join()
的“on clause”,即指示如何构建 JOIN 的“ON”部分。
提示
请注意,使用Select.join()
从一个实体 JOIN 到另一个实体会影响 SELECT 语句的 FROM 子句,但不影响列子句;在这个示例中,SELECT 语句仍将只返回来自User
实体的行。要同时从User
和Address
中 SELECT 列/实体,必须在select()
函数中也命名Address
实体,或者使用Select.add_columns()
方法在之后将其添加到Select
构造中。有关这两种形式的示例,请参见同时选择多个 ORM 实体部分。
链接多个表
要构建一系列 JOIN,可以使用多个Select.join()
调用。关系绑定属性同时暗示 JOIN 的左侧和右侧。考虑额外的实体Order
和Item
,其中User.orders
关系指向Order
实体,而Order.items
关系通过关联表order_items
指向Item
实体。两个Select.join()
调用将导致从User
到Order
的第一个 JOIN,以及从Order
到Item
的第二个 JOIN。然而,由于Order.items
是多对多关系,它会导致两个独立的 JOIN 元素,总共在生成的 SQL 中有三个 JOIN 元素:
>>> stmt = select(User).join(User.orders).join(Order.items) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN user_order ON user_account.id = user_order.user_id JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id JOIN item ON item.id = order_items_1.item_id
每次调用Select.join()
方法的顺序只有在我们希望连接的“左”侧需要在 FROM 列表中存在之前才会产生影响。例如,如果我们指定了select(User).join(Order.items).join(User.orders)
,那么Select.join()
将无法正确连接,并且会引发错误。在正确的实践中,应以类似于 SQL 中 JOIN 子句应该呈现的方式调用Select.join()
方法,并且每次调用应该代表与其前面的内容之间的清晰链接。
我们在 FROM 子句中定位的所有元素仍然可以作为继续连接 FROM 的潜在点。例如,我们可以在上面的User
实体上继续添加其他元素以连接 FROM,例如在我们的连接链中添加User.addresses
关系:
>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN user_order ON user_account.id = user_order.user_id JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id JOIN item ON item.id = order_items_1.item_id JOIN address ON user_account.id = address.user_id
连接到目标实体
Select.join()
的第二种形式允许任何映射实体或核心可选择的构造作为目标。在这种用法中,Select.join()
将尝试推断连接的 ON 子句,使用两个实体之间的自然外键关系:
>>> stmt = select(User).join(Address) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id
在上述调用形式中,Select.join()
被调用来自动推断“on 子句”。如果两个映射的Table
构造之间没有设置任何ForeignKeyConstraint
,或者如果它们之间存在多个ForeignKeyConstraint
链接,使得要使用的适当约束不明确,这种调用形式最终将引发错误。
注意
当使用 Select.join()
或 Select.join_from()
而没有指定 ON 子句时,ORM 配置的 relationship()
构建不会考虑。只有在尝试推断 JOIN 的 ON 子句时,才会查询映射的 Table
对象级别的实体之间配置的 ForeignKeyConstraint
关系。
加入带有 ON 子句的目标
第三种调用形式允许同时显式传递目标实体和 ON 子句。一个包含 SQL 表达式作为 ON 子句的示例如下:
>>> stmt = select(User).join(Address, User.id == Address.user_id) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id
表达式基于的 ON 子句也可以是 relationship()
绑定的属性,就像在 简单 Relationship 加入 中使用的方式一样:
>>> stmt = select(User).join(Address, User.addresses) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id
上述示例似乎冗余,因为它以两种不同的方式指示了 Address
的目标;然而,当加入别名实体时,这种形式的实用性就变得明显了;请参见 使用 Relationship 在别名目标之间加入 部分的示例。
将 Relationship 与自定义 ON 条件相结合
relationship()
构建生成的 ON 子句可以通过附加的条件进行增强。这对于快速限制特定关系路径上连接的范围的方法以及配置加载器策略(例如 joinedload()
和 selectinload()
)等情况都很有用。PropComparator.and_()
方法接受一系列 SQL 表达式,这些表达式将通过 AND 连接到 JOIN 的 ON 子句中。例如,如果我们想要从 User
加入到 Address
,但也只限制 ON 条件到某些电子邮件地址:
>>> stmt = select(User.fullname).join( ... User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org") ... ) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id AND address.email_address = ? [...] ('squirrel@squirrelpower.org',) [('Sandy Cheeks',)]
另请参见
PropComparator.and_()
方法也适用于加载策略,如joinedload()
和selectinload()
。参见向加载选项添加条件一节。
使用关系连接别名目标
当使用relationship()
绑定的属性来指示 ON 子句构建连接时,可以将具有 ON 子句的目标的连接中示例的二参数语法扩展到与aliased()
构造一起工作,以指示 SQL 别名作为连接的目标,同时仍然利用relationship()
绑定的属性来指示 ON 子句,如下例所示,其中User
实体两次与两个不同的aliased()
构造连接到Address
实体:
>>> address_alias_1 = aliased(Address) >>> address_alias_2 = aliased(Address) >>> stmt = ( ... select(User) ... .join(address_alias_1, User.addresses) ... .where(address_alias_1.email_address == "patrick@aol.com") ... .join(address_alias_2, User.addresses) ... .where(address_alias_2.email_address == "patrick@gmail.com") ... ) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user_account.id = address_2.user_id WHERE address_1.email_address = :email_address_1 AND address_2.email_address = :email_address_2
使用修饰符PropComparator.of_type()
可以更简洁地表达相同的模式,该修饰符可以应用于relationship()
绑定的属性,通过传递目标实体以一步指示目标。下面的示例使用PropComparator.of_type()
来生成与刚刚示例相同的 SQL 语句:
>>> print( ... select(User) ... .join(User.addresses.of_type(address_alias_1)) ... .where(address_alias_1.email_address == "patrick@aol.com") ... .join(User.addresses.of_type(address_alias_2)) ... .where(address_alias_2.email_address == "patrick@gmail.com") ... ) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user_account.id = address_2.user_id WHERE address_1.email_address = :email_address_1 AND address_2.email_address = :email_address_2
要利用relationship()
构建从别名实体的连接,可以直接从aliased()
构造中使用属性:
>>> user_alias_1 = aliased(User) >>> print(select(user_alias_1.name).join(user_alias_1.addresses)) SELECT user_account_1.name FROM user_account AS user_account_1 JOIN address ON user_account_1.id = address.user_id
连接到子查询
加入的目标可以是任何“可选择”的实体,包括子查询。在使用 ORM 时,通常会使用 aliased()
构造来表示这些目标,但这不是严格要求的,特别是如果加入的实体不会在结果中返回的情况下。例如,要从 User
实体加入到 Address
实体,其中 Address
实体被表示为一行限制的子查询,我们首先使用 Select.subquery()
构造一个 Subquery
对象,然后可以将其用作 Select.join()
方法的目标:
>>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery() >>> stmt = select(User).join(subq, User.id == subq.c.user_id) >>> print(stmt) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN (SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address FROM address WHERE address.email_address = :email_address_1) AS anon_1 ON user_account.id = anon_1.user_id
上述 SELECT 语句在通过 Session.execute()
调用时将返回包含 User
实体但不包含 Address
实体的行。为了将 Address
实体包含到将在结果集中返回的实体集中,我们针对 Address
实体和 Subquery
对象构造了一个 aliased()
对象。我们可能还希望对 aliased()
构造应用一个名称,例如下面使用的 "address"
,以便我们可以通过名称在结果行中引用它:
>>> address_subq = aliased(Address, subq, name="address") >>> stmt = select(User, address_subq).join(address_subq) >>> for row in session.execute(stmt): ... print(f"{row.User} {row.address}") SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address FROM user_account JOIN (SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address FROM address WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id [...] ('pat999@aol.com',) User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')
沿着关系路径加入子查询
前面部分中示例的子查询形式可以使用一个 relationship()
绑定属性更具体地表示,使用 使用 Relationship 在别名目标之间加入 中指示的其中一种形式。例如,为了创建相同的加入并确保加入是沿着特定 relationship()
进行的,我们可以使用 PropComparator.of_type()
方法,传递包含加入目标的 aliased()
构造的 Subquery
对象:
>>> address_subq = aliased(Address, subq, name="address") >>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq)) >>> for row in session.execute(stmt): ... print(f"{row.User} {row.address}") SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address FROM user_account JOIN (SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address FROM address WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id [...] ('pat999@aol.com',) User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')
引用多个实体的子查询
包含跨越多个 ORM 实体的列的子查询可以同时应用于多个aliased()
构造,并且在每个实体的情况下都可以在相同的Select
构造中使用。生成的 SQL 将继续将所有这样的aliased()
构造视为相同的子查询,但是从 ORM / Python 的角度来看,可以通过使用适当的aliased()
构造来引用不同的返回值和对象属性。
给定一个同时引用User
和Address
的子查询,例如:
>>> user_address_subq = ( ... select(User.id, User.name, User.fullname, Address.id, Address.email_address) ... .join_from(User, Address) ... .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"])) ... .subquery() ... )
我们可以创建针对User
和Address
的aliased()
构造,它们各自都引用相同的对象:
>>> user_alias = aliased(User, user_address_subq, name="user") >>> address_alias = aliased(Address, user_address_subq, name="address")
从两个实体中选择的Select
构造将只渲染子查询一次,但在结果行上下文中可以同时返回User
和Address
类的对象:
>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy") >>> for row in session.execute(stmt): ... print(f"{row.user} {row.address}") SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname, address.id AS id_1, address.email_address AS email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE address.email_address IN (?, ?)) AS anon_1 WHERE anon_1.name = ? [...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy') User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')
设置连接中最左边的 FROM 子句
在当前Select
状态的左侧与我们要连接的内容不一致的情况下,可以使用Select.join_from()
方法:
>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1
Select.join_from()
方法接受两个或三个参数,形式可以是(, )
,或者是(, , [])
:
>>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1
为了为 SELECT 设置初始 FROM 子句,以便之后可以使用Select.join()
,也可以使用Select.select_from()
方法:
>>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1
提示
Select.select_from()
方法实际上并没有最终决定 FROM 子句中表的顺序。如果语句还引用了一个Join
构造,该构造引用了不同顺序的现有表,则Join
构造优先。当我们使用像Select.join()
和Select.join_from()
这样的方法时,这些方法最终会创建这样一个Join
对象。因此,在这种情况下,我们可以看到Select.select_from()
的内容被覆盖:
>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy") >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = :name_1
在上述例子中,我们看到 FROM 子句是address JOIN user_account
,尽管我们首先声明了select_from(User)
。由于.join(Address.user)
方法调用,语句最终等效于以下内容:
>>> from sqlalchemy.sql import join >>> >>> user_table = User.__table__ >>> address_table = Address.__table__ >>> >>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id) >>> stmt = ( ... select(address_table) ... .select_from(user_table) ... .select_from(j) ... .where(user_table.c.name == "sandy") ... ) >>> print(stmt) SELECT address.id, address.user_id, address.email_address FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = :name_1
上述Join
构造是作为Select.select_from()
列表中的另一个条目添加的,它取代了先前的条目。
关系 WHERE 运算符
除了在Select.join()
和Select.join_from()
方法中使用relationship()
构造之外,relationship()
还在帮助构造通常用于 WHERE 子句的 SQL 表达式,使用Select.where()
方法。
EXISTS 形式:has() / any()
Exists
构造首次在 SQLAlchemy 统一教程的 EXISTS 子查询部分中介绍。该对象用于在标量子查询与 SQL EXISTS 关键字一起呈现。relationship()
构造提供了一些辅助方法,可以用于以关系的方式生成一些常见的 EXISTS 风格的查询。
对于像User.addresses
这样的一对多关系,可以使用PropComparator.any()
针对与user_account
表相关联的address
表进行 EXISTS 查询。此方法接受一个可选的 WHERE 条件来限制子查询匹配的行:
>>> stmt = select(User.fullname).where( ... User.addresses.any(Address.email_address == "squirrel@squirrelpower.org") ... ) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account WHERE EXISTS (SELECT 1 FROM address WHERE user_account.id = address.user_id AND address.email_address = ?) [...] ('squirrel@squirrelpower.org',) [('Sandy Cheeks',)]
由于 EXISTS 倾向于更有效地进行负查找,一个常见的查询是定位没有相关实体的实体。这可以简洁地使用短语~User.addresses.any()
来实现,以选择没有相关Address
行的User
实体:
>>> stmt = select(User.fullname).where(~User.addresses.any()) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account WHERE NOT (EXISTS (SELECT 1 FROM address WHERE user_account.id = address.user_id)) [...] () [('Eugene H. Krabs',)]
PropComparator.has()
方法的工作方式与PropComparator.any()
基本相同,只是它用于一对多关系,例如,如果我们想要定位所有属于“sandy”的Address
对象:
>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy")) >>> session.execute(stmt).all() SELECT address.email_address FROM address WHERE EXISTS (SELECT 1 FROM user_account WHERE user_account.id = address.user_id AND user_account.name = ?) [...] ('sandy',) [('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)] ```### 关系实例比较运算符 `relationship()`-绑定的属性还提供了一些 SQL 构造实现,这些实现旨在根据相关对象的特定实例来过滤`relationship()`-绑定的属性,它可以从给定的持久(或较少见的分离)对象实例中解包适当的属性值,并构造 WHERE 条件,以便针对目标`relationship()`。 + **一对多等于比较** - 可以将特定对象实例与一对多关系进行比较,以选择外键与给定对象的主键值匹配的行: ```py >>> user_obj = session.get(User, 1) SELECT ... >>> print(select(Address).where(Address.user == user_obj)) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id ``` + **一对多不等于比较** - 也可以使用不等于运算符: ```py >>> print(select(Address).where(Address.user != user_obj)) SELECT address.id, address.user_id, address.email_address FROM address WHERE address.user_id != :user_id_1 OR address.user_id IS NULL ``` + **对象包含在一对多集合中** - 这基本上是“等于”比较的一对多版本,选择主键等于相关对象中的外键值的行: ```py >>> address_obj = session.get(Address, 1) SELECT ... >>> print(select(User).where(User.addresses.contains(address_obj))) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.id = :param_1 ``` + **从一对多的角度来看,对象有一个特定的父对象** - `with_parent()`函数生成一个比较,返回被给定父对象引用的行,这本质上与使用一对多方的`==`运算符相同: ```py >>> from sqlalchemy.orm import with_parent >>> print(select(Address).where(with_parent(user_obj, User.addresses))) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id ```### EXISTS forms: has() / any() `Exists`构造首次在 SQLAlchemy 统一教程中的 EXISTS 子查询一节中引入。该对象用于在标量子查询与 SQL EXISTS 关键字一起渲染。`relationship()`构造提供了一些辅助方法,可以用于根据关系生成一些常见的 EXISTS 风格的查询。 对于像`User.addresses`这样的一对多关系,可以使用与`user_account`表关联的`address`表的 EXISTS 来生成 `PropComparator.any()`。此方法接受一个可选的 WHERE 条件来限制子查询匹配的行: ```py >>> stmt = select(User.fullname).where( ... User.addresses.any(Address.email_address == "squirrel@squirrelpower.org") ... ) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account WHERE EXISTS (SELECT 1 FROM address WHERE user_account.id = address.user_id AND address.email_address = ?) [...] ('squirrel@squirrelpower.org',) [('Sandy Cheeks',)]
由于 EXISTS 倾向于对负查询更有效,一个常见的查询是定位那些不存在相关实体的实体。这可以用如~User.addresses.any()
这样的短语来简洁地实现,以选择没有相关Address
行的User
实体:
>>> stmt = select(User.fullname).where(~User.addresses.any()) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account WHERE NOT (EXISTS (SELECT 1 FROM address WHERE user_account.id = address.user_id)) [...] () [('Eugene H. Krabs',)]
PropComparator.has()
方法的工作方式基本与PropComparator.any()
相同,只是它用于多对一的关系,比如我们想要定位所有属于“sandy”的Address
对象:
>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy")) >>> session.execute(stmt).all() SELECT address.email_address FROM address WHERE EXISTS (SELECT 1 FROM user_account WHERE user_account.id = address.user_id AND user_account.name = ?) [...] ('sandy',) [('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]
Relationship Instance Comparison Operators
relationship()
绑定属性还提供了一些 SQL 构建实现,用于基于特定相关对象的实例来过滤relationship()
绑定属性,这可以从给定的持久(或更少见的分离)对象实例中解包适当的属性值,并根据目标relationship()
构造 WHERE 条件。
- 多对一等于比较 - 一个特定的对象实例可以与多对一关系进行比较,以选择外键与目标实体的主键值匹配的行:
>>> user_obj = session.get(User, 1) SELECT ... >>> print(select(Address).where(Address.user == user_obj)) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id
- 多对一不等于比较 - 也可以使用不等于运算符:
>>> print(select(Address).where(Address.user != user_obj)) SELECT address.id, address.user_id, address.email_address FROM address WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
- 对象包含在一对多集合中 - 这基本上是“等于”比较的一对多版本,选择主键等于相关对象中外键值的行:
>>> address_obj = session.get(Address, 1) SELECT ... >>> print(select(User).where(User.addresses.contains(address_obj))) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.id = :param_1
- 从一对多的角度看,对象有一个特定的父对象 -
with_parent()
函数生成一个比较,返回由给定父对象引用的行,这与使用==
运算符与多对一方面基本相同:
>>> from sqlalchemy.orm import with_parent >>> print(select(Address).where(with_parent(user_obj, User.addresses))) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id
, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = :param_1
```
- 从一对多的角度来看,对象有一个特定的父对象 -
with_parent()
函数生成一个比较,返回被给定父对象引用的行,这本质上与使用一对多方的==
运算符相同:
>>> from sqlalchemy.orm import with_parent >>> print(select(Address).where(with_parent(user_obj, User.addresses))) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id ```### EXISTS forms: has() / any()
Exists
构造首次在 SQLAlchemy 统一教程中的 EXISTS 子查询一节中引入。该对象用于在标量子查询与 SQL EXISTS 关键字一起渲染。relationship()
构造提供了一些辅助方法,可以用于根据关系生成一些常见的 EXISTS 风格的查询。
对于像User.addresses
这样的一对多关系,可以使用与user_account
表关联的address
表的 EXISTS 来生成 PropComparator.any()
。此方法接受一个可选的 WHERE 条件来限制子查询匹配的行:
>>> stmt = select(User.fullname).where( ... User.addresses.any(Address.email_address == "squirrel@squirrelpower.org") ... ) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account WHERE EXISTS (SELECT 1 FROM address WHERE user_account.id = address.user_id AND address.email_address = ?) [...] ('squirrel@squirrelpower.org',) [('Sandy Cheeks',)]
由于 EXISTS 倾向于对负查询更有效,一个常见的查询是定位那些不存在相关实体的实体。这可以用如~User.addresses.any()
这样的短语来简洁地实现,以选择没有相关Address
行的User
实体:
>>> stmt = select(User.fullname).where(~User.addresses.any()) >>> session.execute(stmt).all() SELECT user_account.fullname FROM user_account WHERE NOT (EXISTS (SELECT 1 FROM address WHERE user_account.id = address.user_id)) [...] () [('Eugene H. Krabs',)]
PropComparator.has()
方法的工作方式基本与PropComparator.any()
相同,只是它用于多对一的关系,比如我们想要定位所有属于“sandy”的Address
对象:
>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy")) >>> session.execute(stmt).all() SELECT address.email_address FROM address WHERE EXISTS (SELECT 1 FROM user_account WHERE user_account.id = address.user_id AND user_account.name = ?) [...] ('sandy',) [('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]
Relationship Instance Comparison Operators
relationship()
绑定属性还提供了一些 SQL 构建实现,用于基于特定相关对象的实例来过滤relationship()
绑定属性,这可以从给定的持久(或更少见的分离)对象实例中解包适当的属性值,并根据目标relationship()
构造 WHERE 条件。
- 多对一等于比较 - 一个特定的对象实例可以与多对一关系进行比较,以选择外键与目标实体的主键值匹配的行:
>>> user_obj = session.get(User, 1) SELECT ... >>> print(select(Address).where(Address.user == user_obj)) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id
- 多对一不等于比较 - 也可以使用不等于运算符:
>>> print(select(Address).where(Address.user != user_obj)) SELECT address.id, address.user_id, address.email_address FROM address WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
- 对象包含在一对多集合中 - 这基本上是“等于”比较的一对多版本,选择主键等于相关对象中外键值的行:
>>> address_obj = session.get(Address, 1) SELECT ... >>> print(select(User).where(User.addresses.contains(address_obj))) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.id = :param_1
- 从一对多的角度看,对象有一个特定的父对象 -
with_parent()
函数生成一个比较,返回由给定父对象引用的行,这与使用==
运算符与多对一方面基本相同:
>>> from sqlalchemy.orm import with_parent >>> print(select(Address).where(with_parent(user_obj, User.addresses))) SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id