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).
