在jOOQ中将unnest用作字段而不是表

Question

这是我试图在PostgreSQL中运行的查询：

SELECT * FROM message WHERE id IN (
    SELECT unnest(message_ids) "mid"
        FROM session_messages WHERE session_id = '?' ORDER BY "mid" ASC
);

然而，我不能做一些事情：

create.selectFrom(Tables.MESSAGE).where(Tables.MESSAGE.ID.in(
    create.select(DSL.unnest(..))

因为DSL.unnest是一个Table<?>，这是有意义的，因为它试图接受一个List-like对象(主要是文字)并将其转换为表。

我有一种感觉，我需要找到一个方法来包装我的字段名，但我不知道如何进行。

请注意。字段message_ids为bigint[]类型。

编辑

所以，我现在就是这样做的，它的工作方式和预期完全一样，但我不确定这是否是最好的方法：

Field<Long> unnestMessageIdField = DSL.field(
                "unnest(" +  SESSION_MESSAGES.MESSAGE_IDS.getName() + ")",
                Long.class)
        .as("mid");

Field<Long> messageIdField = DSL.field("mid", Long.class);

MESSAGE.ID.in(
        ctx.select(messageIdField).from(
            ctx.select(unnestMessageIdField)
               .from(Tables.CHAT_SESSION_MESSAGES)
                    .where(Tables.CHAT_SESSION_MESSAGES.SESSION_ID.eq(sessionId))
            )
            .where(condition)
)

EDIT2

在研究了https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DSL.java上的代码之后，我想正确的方法是：

DSL.function("unnest", SQLDataTypes.BIGINT.getArrayType(), SESSION_MESSAGES.MESSAGE_IDS)

EDIT3

由于lukas总是为我的jOOQ灾难而来，我将利用这一点:)

试图在排序签名中泛化此函数

public <T> Field<T> unnest(Field<T[]> arrayField) {
    return DSL.function("unnest", <??>, arrayField);
}

我不知道怎样才能获取数据类型。似乎有一种方法可以使用DataType<T[]>从DataType<T>获得DataType::getArrayDataType()，但不可能出现相反的情况。有一个类我找到了ArrayDataType，但是它似乎是包私有的，所以我不能使用它(即使可以，它也不会公开字段elementType)。

Answer 1

旧的PostgreSQL版本有这样一个奇怪的想法:从SELECT子句中生成一个表，并将它扩展到“外部”表，就像在FROM子句中声明它一样。这是一个非常晦涩的PostgreSQL遗留问题，这个例子是摆脱它并使用LATERAL的好机会。您的查询等价于以下查询：

SELECT * 
FROM message 
WHERE id IN (
    SELECT "mid"
    FROM session_messages 
    CROSS JOIN LATERAL unnest(message_ids) AS t("mid")
    WHERE session_id = '?' 
);

这可以更容易地转换为jOOQ，因为：

DSL.using(configuration)
   .select()
   .from(MESSAGE)
   .where(MESSAGE.ID).in(
        select(field(name("mid"), MESSAGE.ID.getDataType()))
       .from(SESSION_MESSAGES)
       .crossJoin(lateral(unnest(SESSION_MESSAGES.MESSAGE_IDS)).as("t", "mid"))
       .where(SESSION_MESSAGES.SESSION_ID.eq("'?'"))
   )

Answer 2

问题中的Edit3非常接近于解决这个问题。我们可以为jOOQ创建一个自定义的泛型jOOQ方法，该方法接受Field并在jOOQ查询中正常使用。

帮助方法：

public static <T> Field<T> unnest(Field<T[]> field, Class<T> type) {
    return DSL.function("unnest", type, field);
}

使用：

public void query(SessionId sessionId) {

    var field = unnest(SESSION_MESSAGES.MESSAGE_IDS, UUID.class);

    dsl.select().from(MESSAGE).where(
            MESSAGE.ID.in(
                dsl.select(field).from(SESSION_MESSAGES)
                .where(SESSION_MESSAGES.SESSION_ID.eq(sessionId.id))
                .orderBy(field)
            )
    );

}