Конкатенация массивов и их преобразование в 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)
);

Я описал CTE. Количество таблиц в 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()

Описал основной запрос:

event_all = session.query(cte_union_view)\
        .join(Valuefield, cte_union_view.c.id_value == Valuefield.idvalfield)\
        .join(Event, Event.idevent == Valuefield.idevent).all()

Но у меня не получается описать само условие WHERE:

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

Пробовал:

filter(cast([Event.idevent + array([])], ARRAY(UUID)).in_(filtered_event))
and 
filter(cast([array([Event.idevent]) + array([])], ARRAY(UUID)).in_(filtered_event))

Но это не работает. Кто знает, как можно это описать на ORM?

Answer 1

В общем, покопавшись в алхимии нашел способ решения. Может будет кому-то полезен. Преобразование 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.notin_(filtered_event)).all()

БЛОГ НА HUSL

Конкатенация массивов и их преобразование в SQLAlchemy

Ответы (1 шт):