sql - Select entity and ids of its surrounding entities - Stack Overflow

admin管理员组

文章数量:1433966

This is my query It is meant to return the selectedResponse and its next and previous ids. As you can see, the Where clause currently constrains the results. I tried a nested select and CTE, both resulted in errors. In my previous implementation, I used nested selects with limit one, that resulted in n+1. I can't use joins, due to the issues with the parent ids (it was getting ids from another requests).

@Query("""
      SELECT
      new code.modules.conversation.data.jpa.projection.ResponseWindow(
        res,
        LAG(res.id) OVER (PARTITION BY res.request ORDER BY res.created),
        LEAD(res.id) OVER (PARTITION BY res.request ORDER BY res.created)
      )
      FROM ResponseEntity res
      WHERE res = :selectedResponse
    """)
    ```

本文标签： sqlSelect entity and ids of its surrounding entitiesStack Overflow