JDOQL Subqueries

With JDOQL the user has a very flexible query syntax which allows for querying of the vast majority of data components in a single query. In some situations it is desirable for the query to utilise the results of a separate query in its calculations. JDO 2.1 provides a very useful addition to JDOQL adding on subqueries, so that both calculations can be performed in one query. Here's an example, using single-string JDOQL 

SELECT FROM org.datanucleus.Employee WHERE salary > 
    (SELECT avg(salary) FROM org.datanucleus.Employee e)

So we want to find all Employees that have a salary greater than the average salary. In single-string JDOQL the subquery must be in parentheses (brackets). Note that we have defined the subquery with an alias of "e", whereas in the outer query the alias is "this".

We can specify the same query using the JDOQL API, like this 

Query averageSalaryQuery = pm.newQuery(Employee.class);
averageSalaryQuery.setResult("avg(this.salary)");

Query q = pm.newQuery(Employee.class, "salary > averageSalary");
q.declareVariables("double averageSalary");
q.addSubquery(averageSalaryQuery, "double averageSalary", null, null);
List results = (List)q.execute();

So we define a subquery as its own Query (that could be executed just like any query if so desired), and the in the main query have an implicit variable that we define as being represented by the subquery.



Referring to the outer query in the subquery

JDOQL subqueries allows use of the outer query fields within the subquery if so desired. Taking the above example and extending it, here is how we do it in single-string JDOQL 

SELECT FROM org.datanucleus.Employee WHERE salary > 
    (SELECT avg(salary) FROM org.datanucleus.Employee e WHERE e.lastName == this.lastName)

So with single-string JDOQL we make use of the alias identifier "this" to link back to the outer query.

Using the JDOQL API, to achieve the same thing we would do 

Query averageSalaryQuery = pm.newQuery(Employee.class);
averageSalaryQuery.setResult("avg(this.salary)");
averageSalaryQuery.setFilter("this.lastName == :lastNameParam");

Query q = pm.newQuery(Employee.class, "salary > averageSalary");
q.declareVariables("double averageSalary");
q.addSubquery(averageSalaryQuery, "double averageSalary", null, "this.lastName");
List results = (List)q.execute();

So with the JDOQL API we make use of parameters, and the last argument to addSubquery is the value of the parameter lastNameParam .



Candidate of the subquery being part of the outer query

There are occasions where we want the candidate of the subquery to be part of the outer query, so JDOQL subqueries has the notion of a candidate expression . This is an expression relative to the candidate of the outer query. An example 

SELECT FROM org.datanucleus.Employee WHERE this.weeklyhours > 
    (SELECT AVG(e.weeklyhours) FROM this.department.employees e)

so the candidate of the subquery is this.department.employees . If using a candidate expression we must provide an alias.

You can do the same with the JDOQL API. Like this 

Query averageHoursQuery = pm.newQuery(Employee.class);
averageHoursQuery.setResult("avg(this.weeklyhours)");

Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyhours > averageWeeklyhours");
q.addSubquery(averageHoursQuery, "double averageWeeklyhours", "this.department.employees", null);

so now our subquery has a candidate related to the outer query candidate.