数组的连接及其到 SQLAlchemy 的转换

Question

我有下一个 PostgreSQL 查询：

WITH sub_query AS (

    SELECT vi.idvalfield,

           vi.value::text

    FROM valueint_value AS vi

    UNION

    SELECT vt.idvalfield,

           vt.value::text

    FROM valuetext_value AS vt

)

SELECT

       sq.idvalfield,

       sq.value

FROM sub_query AS sq

         JOIN valuefield AS vf ON vf.idvalfield = sq.idvalfield

         JOIN event e on vf.idevent = e.idevent

WHERE NOT (e.idevent || array[]::uuid[]) && (SELECT array_agg(e.idevent) AS id_event

                                              FROM sub_query AS sq

                                                       JOIN valuefield AS vf ON vf.idvalfield = sq.idvalfield

                                                       JOIN event e on vf.idevent = e.idevent

                                              WHERE (idtable = 41 AND sq.value = 222)

                                                 OR (idtable = 43 AND sq.value = 18)

);

我描述了WITH. UNION 中的表数是动态的：

from sqlalchemy.dialects import postgresql

from sqlalchemy import or_, and_

from sqlalchemy import cast, Table, Text

from sqlalchemy.dialects.postgresql import array_agg, array, ARRAY, UUID

models_view = [

        session.query(

            model.c.idvalfield.label('id_value'),

            cast(model.c.value, Text).label('value')

        ).filter(model.c.idvalfield.in_(id_fields))

        for model, id_fields in model_values.items()

    ]

cte_union_view = models_view[0].union_all(*models_view[1:]).cte()

在 WHERE 中描述了一个子查询：

filtered_event = session.query(array_agg(Event.idevent))\

        .select_from(cte_union_view)\

        .join(Valuefield, cte_union_view.c.id_value == Valuefield.idvalfield)\

        .join(Event, Event.idevent == Valuefield.idevent)\

        .filter(or_(and_(Valuefield.idtable == 41, cte_union_view.c.value == '222'),

                    and_(Valuefield.idtable == 43, cte_union_view.c.value == '18'))).subquery()

Answer 1

您还可以使用：array(tuple_(Event.idevent), type_=UUID)

Answer 2

找到了解决方案。也许它对某人有用。将 UUID 转换为 UUID 数组：(e.idevent || array[]::uuid[])你可以这样做literal_column：literal_column('ARRAY[]::uuid[]').op('||')(Event.idevent)现在整个 `WHERE' 块可以这样描述：.filter(uuid_event_arr.notin_(filtered_event)).all()但是，事实上，在WHERE没有 'array_agg ()' 函数的情况下重写子查询更容易。这反过来又使使用炼金术构建查询变得更加容易：filtered_event = session.query(Event.idevent)\        .select_from(cte_union_view)\        .join(Valuefield, cte_union_view.c.id_value == Valuefield.idvalfield)\        .join(Event, Event.idevent == Valuefield.idevent)\        .filter(and_(Valuefield.idtable == 41, cte_union_view.c.v_text == '222'))views_value = session.query(cte_union_view)\        .join(Valuefield, cte_union_view.c.id_value == Valuefield.idvalfield)\        .join(Event, Event.idevent == Valuefield.idevent)\        .filter(Event.idevent.in_(filtered_event)).all()