CODEWITHSUNDEEP
sqlspringhibernatejpahql
monandszy
monandszy

Reputation: 1

Select entity and ids of its surrounding entities

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
    """)
    ```

Upvotes: 0

Views: 26

Answers (0)

Related Questions