postgresql 在jOOQ中使用unnest作为字段而不是表

mhd8tkvw 于 2023-01-17 发布在 PostgreSQL

关注(0)|答案(2)|浏览(210)

这是我试图在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的对象（主要是一个文本）并将其转换为表。
我有一种感觉，我需要找到一种方法来 Package 我的字段名的函数，但我不知道如何进行。
注意：字段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)
)

编辑2
在浏览了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)

编辑3
既然卢卡斯一如既往地在这里为我的工作烦恼，我要利用这一点：）
尝试将此函数泛化为sort签名

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

我不知道如何获取数据类型。似乎有一种方法可以使用DataType::getArrayDataType()从DataType<T>获取DataType<T[]>，但反过来是不可能的。我找到了ArrayDataType这个类，但它似乎是包私有的，所以我不能使用它（即使我可以，它也没有暴露字段elementType）。

postgresql

来源：https://stackoverflow.com/questions/36572388/using-unnest-as-a-field-rather-than-a-table-in-jooq

2条答案

按热度按时间

7gcisfzg1#

旧版本的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("'?'"))
   )

赞(0）回复(0）举报 2023-01-17

qyzbxkaa2#

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

帮助器方法：

public static <T> Field<T> unnest(Field<T[]> field) {
    var type = (Class<T>) field.getType().getComponentType();
    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)
            )
    );

}

赞(0）回复(0）举报 2023-01-17

我来回答

postgresql 在jOOQ中使用unnest作为字段而不是表

2条答案

相关问题

热门标签

最新问答