与集合返回函数(SRF)和SQLAlchemy中的访问列联接

问题描述

假设我有一个activity表和一个subscription表.每个活动都具有对某个其他对象的通用引用的数组，每个订阅都具有对同一集中的其他对象的单个通用引用.

Suppose I have an activity table and a subscription table. Each activity has an array of generic references to some other object, and each subscription has a single generic reference to some other object in the same set.

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，因此我可以找到最深"匹配的订阅，如下所示:

I want to join with the set-returning function unnest so I can find the "deepest" matching subscription, something like this:

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 像这样:

But I can't figure out how to do that second join and get access to the columns. I've tried creating a 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带有另一个子查询:

But that results in this SQL with another subquery:

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]

...由于缺少和不受支持 LATERAL关键字:

... which fails because of the missing and unsupported LATERAL keyword:

那么，如何在不使用子查询的情况下为该SRF创建一个JOIN子句?还是我想念其他东西?

So, how can I create a JOIN clause for this SRF without using a subquery? Or is there something else I'm missing?

编辑1 使用 sa.text 与 TextClause.columns 而不是sa.select使我更接近:

Edit 1 Using sa.text with TextClause.columns instead of sa.select gets me a lot closer:

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失败，因为它将子句括在括号中

But the resulting SQL fails because it wraps the clause in parentheses:

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

错误为syntax error at or near ")".我可以把TextAsFrom放在括号中吗?

The error is syntax error at or near ")". Can I get TextAsFrom to not be wrapped in parentheses?

推荐答案

事实证明，SA不直接支持此功能，但是可以使用 ColumnClause 和 FunctionElement .首先导入此食谱，如在此SA问题.然后创建一个包含WITH ORDINALITY修饰符的特殊unnest函数:

It turns out this is not directly supported by SA, but the correct behaviour can be achieved with a ColumnClause and a FunctionElement. First import this recipe as described by zzzeek in this SA issue. Then create a special unnest function that includes the WITH ORDINALITY modifier:

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"

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

You can then use it in joins, ordering, etc. like this:

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

这篇关于与集合返回函数(SRF)和SQLAlchemy中的访问列联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！