JPA get the previous and next record in select

Question

I have a JPQL query that retrieves the sorted list of entities from the database. I have the id of one of these entities. How can I get the previous and next record to the record with the known id in my select?

select distinct n from News n 
    inner join n.author a 
    inner join n.tags t 
    where a.id = :authorId and t.id in :tagsId 
    order by size(n.comments) desc

News has one author, many tags and comments. I select news with the given author and tags and order them by the count of the comments to them.

While using JDBC I've solved such problems using rownum.

Can I get the position(rownum) of a record in a result using JPA? If I knew the record's position I could define the first result to that position - 1, and max results to 3 for the given query. That is how I could get the previous, current and next news.

Are there any other solutions, except iterating through the list of news?

Answer 1

I'm pretty sure something better can be done, but this was my workaround when facing a similar issue :

SELECT l.id FROM Lines l WHERE l.date < :date ORDER BY l.date DESC

What I wanted was to retrieve the id of the previous date ("previous" meaning is to be adapted to your case). Detailed steps are as follow :

• WHERE l.date < :date - you select all the records that are considered "below" (in my case, earlier in time)
• ORDER BY l.date DESC - you order that list by date, in a descending direction
• SELECT l.id FROM Lines l - you return the ids of that "half and ordered list"
• The one I was looking for was obviously the first of that list (if the list is not empty)

The idea is to split the result in two main lists, one with all the results "above" what you want and the other one with the results "below". Then you order them and get the first result.

I think it can be improved into a single query instead of two, but I'm not sure how.

Hope it can help you or others with similar questions.