HQL nested subqueries

Question

I have such domain: Country has cities, city has offices, offices has services. I need to find all countries with offices which provide specified service id. My current version is:

SELECT c FROM Country c 
WHERE EXISTS(
             SELECT ct FROM c.cities ct 
             WHERE EXISTS(
                          SELECT o FROM ct.offices o 
                          WHERE EXISTS(
                                       SELECT s.id FROM o.services s 
                                       WHERE s.id = :id
                                      )
                         ) 
            )

So, I'm new to HQL. What is best way to this? Is my version Ok? I was thinking about SELECT DISTINCT with LEFT JOIN too.

Answer 1

I don't think there is anything wrong with your query, but this might be more readable.

SELECT c FROM Country
WHERE EXISTS (
    SELECT s.id FROM
    c.cities ct
    JOIN ct.offices o
    JOIN o.services s
    WHERE s.id = :id
)