0 Replies Latest reply on Mar 9, 2010 7:03 AM by Damian Sinczak

    JPQL along with CASE clause and nested SELECTs

    Damian Sinczak Newbie

      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?