I have two example tables in my DB:
CONTENT - some kind of localized content
- id
- content
- location_id
LOCATION - locations
- id
- name
- parent id - (e.g. parent for city would be provice, for province country and so on)
What I would like to do is to get localized content from DB in proper order. I know that content last time send had Id e.g. 10 and desired location is London. The content I would like to get no have to have id greater that 10 and location London (or it's parent to n level). Another thing would be that if there is no content for id grater that 10 then I would like to receive the one with the smallest id.
So we have here 2 different tasks:
1) localization:
find content from localization X. if content does not exist find content for X parent localization which is Y. If there is no content for Y find one for content for localization Z which is parent of Y. And so on until there is no parent or number of levels N is reached.
2) sequential search
find content with id greater than A, if content does not exist set A to 0 and try to find first content available. So we have some kind of cycle when all content was retrieved, lets start from the beginning.
It's not a problem to do this in code, but is there a possibility to achieve this in JPQL so I don't have to get a lot of data into memory and then chec for id sequence or for location?
