JPA gets the previous and next entry in
I have a JPQL query that retrieves a sorted list of entities from a database. I have the ID of one of these objects. How can I get the previous and next record in a record with a known id in my selection?
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
There is one author in the news, many tags and comments. I select news with a given author and tags and order them according to the number of comments to them.
When using JDBC, I solved such problems by using rownum
.
Can I get the position (rownum) of a record in the result using JPA? If I knew the position of the record, I could determine the first result at that position - 1 and the maximum result up to 3 for a given query. This is how I could get the previous, current and next news.
Are there other solutions besides repeating the news list?
source to share
I'm pretty sure something better could be done, but this was my workaround when I faced a similar problem:
SELECT l.id FROM Lines l WHERE l.date < :date ORDER BY l.date DESC
I wanted to get the id of the previous date (the "previous" meaning should be adapted to your case). The detailed steps are as follows:
- WHERE l.date <: date - you select all records that are considered "below" (in my case, before)
- ORDER BY l.date DESC - you are ordering this list by date in descending order
- SELECT l.id FROM Lines l - you return the IDs of this "half and sorted list"
- The one I was looking for was obviously the first on this list (unless the list is empty)
The idea is to split the result in two main lists, one with all the "above" results that you want, and the other with "below" results. Then you order them and get the first result.
I think this could be improved in one query instead of two, but I'm not sure how.
Hope this can help you or others with similar questions.
source to share