The JDO specification does not provide a simple way to embed one query within another. The best you can do is perform the "inner" query, and use its result(s) as a parameter to the "outer" query. Kodo JDO Enterprise Edition corrects this oversight with full support for JDOQL subqueries.
Kodo's JDOQL subqueries utilize JDO 2's single-string JDOQL syntax, explained in Section 11.9, “Single-String JDOQL”. Kodo makes one minor addition to the single-string format, requiring a logical alias for the subquery candidates. As you'll see below, this allows you to create correlated subqueries that include values from the parent query. The results of a subquery are supplied to the parent query as either a single value or a collection, depending on usage. Let's examine some examples of subqueries in action.
Example 13.3. Comparison to Subquery
In this example, we find all magazines that are tied for the highest cover price.
Query q = pm.newQuery (Magazine.class, "price == (select max(m.price) "
+ "from org.mag.Magazine m)");
Collection mags = (Collection) q.execute ();
Notice that the example surrounds its subquery in parentheses. This is required for all subqueries. Also, note the use of the m alias for subquery candidates. The candidate alias is also required. Without it, Kodo couldn't support correlated subqueries, like the one in the next example.
Example 13.4. Correlated Subquery
In this example, we find all the magazines tied for the highest price within their publisher. The subquery is correlated because it uses the publisher value of the parent query's candidate instance.
Query q = pm.newQuery (Magazine.class, "price == (select max(m.price) "
+ "from org.mag.Magazine m where m.publisher == publisher)");
Collection mags = (Collection) q.execute ();
The previous example used subqueries that were guaranteed to return exactly one result. What if the subquery might return no results, or multiple results? In these cases, you should treat the subquery as a collection. Use contains to test whether a value is included in the subquery results, and isEmpty to test whether the subquery has no results.
Example 13.5. Subquery Contains
Find all magazines whose title matches the title of an article.
Query q = pm.newQuery (Magazine.class,
"(select a.title from org.mag.Article a).contains (title)");
Filtering on whether a subquery contains a value is the same as using the IN operator in SQL.
Example 13.6. Subquery Empty
Find all magazines whose title does not match the title of an article.
Query q = pm.newQuery (Magazine.class,
"(select from org.mag.Article a where a.title == title).isEmpty ()");
Testing whether a subquery is empty is equivalent to SQL's NOT EXISTS assertion. To perform an EXISTS test instead, just negate the expression: !(<subquery>).isEmpty ()
Subqueries cannot declare their own parameters, variables, or imports. The outermost query must always specify all declarations for any subqueries it has, including nested subqueries. Subqueries can, however, introduce new implicit variables and parameters just by using them. For a refresher on JDOQL declarations, including implicit parameters and variables, see Section 11.3, “Advanced Object Filtering”.