gpt4 book ai didi

postgresql - 在 SQLAlchemy 中加入集合返回函数 (SRF) 和访问列

转载 作者:行者123 更新时间:2023-11-29 12:59:43 28 4
gpt4 key购买 nike

假设我有一个activity 表和一个subscription 表。每个事件都有一组对其他对象的通用引用,每个订阅都有一个对同一集合中其他对象的单个通用引用。

CREATE TABLE activity (
id serial primary key,
ob_refs UUID[] not null
);

CREATE TABLE subscription (
id UUID primary key,
ob_ref UUID,
subscribed boolean not null
);

我想加入设置返回函数unnest,这样我就可以找到“最深”的匹配订阅,如下所示:

SELECT id
FROM (
SELECT DISTINCT ON (activity.id)
activity.id,
x.ob_ref, x.ob_depth,
subscription.subscribed IS NULL OR subscription.subscribed = TRUE
AS subscribed,
FROM activity

LEFT JOIN subscription
ON activity.ob_refs @> array[subscription.ob_ref]

LEFT JOIN unnest(activity.ob_refs)
WITH ORDINALITY AS x(ob_ref, ob_depth)
ON subscription.ob_ref = x.ob_ref

ORDER BY x.ob_depth DESC
) sub
WHERE subscribed = TRUE;

但我不知道如何进行第二次连接并访问列。我试过创建一个 FromClause like this :

act_ref_t = (sa.select(
[sa.column('unnest', UUID).label('ob_ref'),
sa.column('ordinality', sa.Integer).label('ob_depth')],
from_obj=sa.func.unnest(Activity.ob_refs))
.suffix_with('WITH ORDINALITY')
.alias('act_ref_t'))

...

query = (query
.outerjoin(
act_ref_t,
Subscription.ob_ref == act_ref_t.c.ob_ref))
.order_by(activity.id, act_ref_t.ob_depth)

但这会导致此 SQL 带有另一个子查询:

LEFT OUTER JOIN (
SELECT unnest AS ob_ref, ordinality AS ref_i
FROM unnest(activity.ob_refs) WITH ORDINALITY
) AS act_ref_t
ON subscription.ob_refs @> ARRAY[act_ref_t.ob_ref]

... 由于缺少和 unsupported 而失败LATERAL 关键字:

There is an entry for table "activity", but it cannot be referenced from this part of the query.

那么,如何在不使用子查询的情况下为此 SRF 创建 JOIN 子句?还是我还缺少其他东西?

编辑 1 使用 sa.textTextClause.columns而不是 sa.select 让我更接近:

act_ref_t = (sa.sql.text(
"unnest(activity.ob_refs) WITH ORDINALITY")
.columns(sa.column('unnest', UUID),
sa.column('ordinality', sa.Integer))
.alias('act_ref'))

但是生成的 SQL 失败了,因为它将子句括在括号中:

LEFT OUTER JOIN (unnest(activity.ob_refs) WITH ORDINALITY)
AS act_ref ON subscription.ob_ref = act_ref.unnest

错误是“)”处或附近的语法错误。我可以让 TextAsFrom 不包含在括号中吗?

最佳答案

事实证明这不是 SA 直接支持的,但是可以通过 ColumnClause 实现正确的行为。和一个 FunctionElement .第一次进口this recipezzzeek 所述在 this SA issue .然后创建一个包含 WITH ORDINALITY 修饰符的特殊 unnest 函数:

class unnest_func(ColumnFunction):
name = 'unnest'
column_names = ['unnest', 'ordinality']

@compiles(unnest_func)
def _compile_unnest_func(element, compiler, **kw):
return compiler.visit_function(element, **kw) + " WITH ORDINALITY"

然后您可以像这样在连接、排序等中使用它:

act_ref = unnest_func(Activity.ob_refs)
query = (query
.add_columns(act_ref.c.unnest, act_ref.c.ordinality)
.outerjoin(act_ref, sa.true())
.outerjoin(Subscription, Subscription.ob_ref == act_ref.c.unnest)
.order_by(act_ref.c.ordinality.desc()))

关于postgresql - 在 SQLAlchemy 中加入集合返回函数 (SRF) 和访问列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33865038/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com