Selecting from path in subquery with CriteriaBuilder?

Question

How is a CriteriaBuilder used to construct subqueries of the form SELECT a FROM e.attributes a .... where e is some entity referenced in the outer query?

I have some entity classes that involve a free form key-value structure (which presents its own problems, but it's what I have). I need to find entities for which certain key-value pairs exist. I can write this as a JPQL query of the form:

SELECT e FROM Entity e 
WHERE e.type = 'foo' 
AND EXISTS (SELECT a FROM e.attributes a 
             WHERE a.key = 'bar' 
               AND a.value = 'baz')

For a fixed query string, I can use create a query with EntityManager.createQuery():

EntityManager em = /* ... */;
TypedQuery<Entity> tq = em.createQuery(queryString, Entity.class);

In practice, there is more than one EXISTS in the query, so I need to construct the query using CriteriaBuilder. The closest I've got so far makes the subquery SELECT a from Attributes a WHERE ..., but that's not restricted to e.attributes, of course:

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Entity> query = cb.createQuery(Entity.class);
Root<Entity> root = query.from(Entity.class);

Subquery<Attribute> subquery = query.subquery(Attribute.class);
Root<Attribute> subroot = subquery.from(Attribute.class); // too broad
subquery.select(subroot)
    .where(cb.and(//
        cb.equal(subroot.get("key"), cb.literal("bar")),
        cb.equal(subroot.get("value"), cb.literal("baz"))));

query.select(root)
    .where(cb.and(//
        cb.equal(root.get("type"), cb.literal("foo")), //
        cb.exists(subquery)));

There are a number of correlate() methods on Subquery, and I wonder if I need to join the Entity with its Attributes in the outer query and then correlate() somehow, but I'm not sure from the EE7 javadocs what exactly correlation does (but correlate() does return a From, which means that I can SELECT from it, which is promising).

Answer 1

I eventually found an answer to this in the JPA 2.0 spec. There's an example on page 276 of a correlated query:

Example 4: A Special case

In order to express some correlated subqueries involving unidirectional relationships, it may be useful to correlate the domain of the subquery with the domain of the containing query. This is performed by using the correlate method of the Subquery interface.

For example:

CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
Root<Customer> customer = q.from(Customer.class);
Subquery<Long> sq = q.subquery(Long.class);
Root<Customer> customerSub = sq.correlate(customer);
Join<Customer,Order> order = customerSub.join(Customer_.orders);
q.where(cb.gt(sq.select(cb.count(order)), 10))
.select(customer);

This query corresponds to the following Java Persistence query language query:

SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10

What threw me for a while, and got me thinking that maybe my JPQL might not be legal, is that the grammar for FROM clauses (p. 173 in the same spec) is given as:

from_clause ::=  FROM  identification_variable_declaration  ...
identification_variable_declaration ::= range_variable_declaration ...
range_variable_declaration ::= entity_name [AS] identification_variable

It wasn't clear to me how something like e.attributes could be an "entity_name". It turns out that there's actually a whole other production in the grammar for subquery FROM clauses that includes the collection support:

subquery_from_clause ::= FROM subselect_identification_variable_declaration ...
subselect_identification_variable_declaration ::=
  identification_variable_declaration |
  derived_path_expression [AS] identification_variable {join}* |
  derived_collection_member_declaration
derived_path_expression ::=
  superquery_identification_variable.{single_valued_object_field.}*collection_valued_field |
  superquery_identification_variable.{single_valued_object_field.}*single_valued_object_field