Subquerying

To completely understand the examples on this page, make sure to read Joining: with and Restrictions (technical) first. The examples can be understood if you only care about the general flow though.

Subquery

In the TypeSafeQueryBuilder, there are two types of queries. The TypeSafeRootQuery and the TypeSafeSubQuery<T>. Each of them have their own set of select(...) methods. The root query cannot be used as a value and has methods to select multiple values (anonymously or into a dto). The subquery can only select a single value and has additional type information, allowing it to be used in as selection value and in restrictions.

To create a subquery, a TypeSafeQuery instance is required. Calling the subquery(Class<T> resultClass) method will return a subquery. Subqueries can be nested and the usage of entities in a query is validated so that they cannot be used outside their scope.

The following code fragment selects peoples favorite colors using a subquery:

Person person = query.from(Person.class);

TypeSafeSubQuery<String> favoriteColorSQ = query.subquery(String.class); // initiate subquery
PersonProperty favColor = favoriteColorSQ.from(PersonProperty.class);
Person personSQ = favoriteColorSQ.join(favColor.getPerson(), JoinType.None);

favoriteColorSQ.select(favColor.getPropertyValue()); // specify the selected value
favoriteColorSQ.where(person.getId()).eq(personSQ.getId()).
                  and(favColor.getPropertyKey()).eq("FavColorKey");

query.selectValue(person);
query.selectValue(favoriteColorSQ);

hqlQuery.getHql() would yield => 
"select hobj1, (select hobj2.propertyValue from PersonProperty hobj2 
         where hobj1.id = hobj2.person.id and hobj2.propertyKey = ?) 
 from Person hobj1"
with params ["FavColorKey"]

Instead of selecting the subquery value, it can also be used in restrictions:

query.whereString(favoriteColorSQ).eq("Blue");

hqlQuery.getHql() would yield => 
"from Person hobj1 where (
    select hobj2.propertyValue from PersonProperty hobj2 
    where hobj1.id = hobj2.person.id and hobj2.propertyKey = ?
 ) = ?"
with params ["FavColorKey", "Blue"]