如何将原生查询改写为JPQL，关联无关表实现排序？

我想把一段原生SQL查询改成JPQL，但卡在了需要用关联表message的字段排序这一步。每个_chat条目可能对应多条_message记录，也可能一条都没有，试了好多次都没成功，甚至不确定JPQL能不能实现。

原生查询实现 #

@Query(
    value = """
        SELECT _chat.*
        FROM _chat
                 LEFT JOIN (SELECT chat_id, max(created_at) as created_at
                            FROM _message
                            GROUP BY chat_id
                ) AS message ON _chat.id = message.chat_id
        WHERE _chat.id in (
            SELECT DISTINCT (chat_id)
            FROM _chat_participant
            WHERE participant_id = ?1
                AND status = 'ACTIVE'
        )
        ORDER BY message.created_at DESC NULLS LAST, _chat.created_at DESC
    """,
    nativeQuery = true
)
fun findAllByUserIdOrderByMessageOrChatCreatedAtDesc(userId: String, pageable: Pageable): Slice<ChatEntity>

未完成的JPQL尝试 #

@EntityGraph(attributePaths = ["participants"])
@Query(
    """
    SELECT DISTINCT c 
    FROM ChatEntity c
    --start of problem part
    LEFT JOIN (                           
        SELECT m FROM MessageEntity m 
        WHERE m.chat.id = c.id 
        ORDER BY m.created_at DESC
        LIMIT 1
    ) 
    -- end of problem part                                   
    LEFT JOIN FETCH c.participants p
    WHERE c.id IN (
        SELECT DISTINCT (pe.chat.id)
        FROM ParticipantEntity pe
        WHERE pe.participantId = :userId and pe.status = 'ACTIVE'
    )
    ORDER BY 
        -- here I have to add max created_at from _message for ordering
        c.createdAt DESC
    """
)
fun findAllByUserIdOrderByMessageOrChatCreatedAtDesc(userId: String): List<ChatEntity>

可行的JPQL改写方案 #

JPQL支持在ORDER BY子句中使用聚合子查询，不需要提前关联子查询表，直接通过子查询获取每个聊天的最新消息时间即可。以下是完整的改写实现：

@EntityGraph(attributePaths = ["participants"])
@Query(
    """
    SELECT DISTINCT c 
    FROM ChatEntity c
    LEFT JOIN FETCH c.participants p
    WHERE c.id IN (
        SELECT DISTINCT pe.chat.id
        FROM ParticipantEntity pe
        WHERE pe.participantId = :userId AND pe.status = 'ACTIVE'
    )
    ORDER BY 
        (SELECT MAX(m.createdAt) FROM MessageEntity m WHERE m.chat = c) DESC NULLS LAST,
        c.createdAt DESC
    """
)
fun findAllByUserIdOrderByMessageOrChatCreatedAtDesc(
    @Param("userId") userId: String,
    pageable: Pageable
): Slice<ChatEntity>

关键说明 #

1. 排序逻辑实现：通过(SELECT MAX(m.createdAt) FROM MessageEntity m WHERE m.chat = c)直接在排序阶段获取每个ChatEntity对应的最新消息时间，替代原生SQL中的LEFT JOIN聚合子表操作。NULLS LAST确保没有消息的聊天排在有消息的聊天之后，和原生逻辑一致。
2. 关联加载优化：保留@EntityGraph和LEFT JOIN FETCH来一次性加载participants关联数据，避免N+1查询问题。
3. 分页支持：恢复Pageable参数并返回Slice，和原生方法的分页能力保持一致。
4. JPQL语法兼容：JPQL不支持在JOIN子句中使用带LIMIT的子查询，改用聚合子查询的方式完美适配JPQL规范，同时实现需求逻辑。

内容的提问来源于stack exchange，提问作者NeverSleeps