I'm trying to configure a subquery in the select clause to achieve an SQL equivalent like this:
SELECT t0.field1 AS a1, t0.field2 AS a2, t0.field3 AS a3, t0.field4 AS a4, t0.field5 AS a5, (SELECT COUNT(t1.id) from table1 t1 WHERE t1.field1t1 = t2.field1t2) AS a6 FROM table0 t0, table2 t2 WHERE (t2.field1t2 = t0.field5)
I found some references on how this could be achieved, like this:
Subquery in select clause with JPA Criteria API
So I made my JPA implementation to build a TupleQuery in order to get, first an Entity (all fields - a1..a5 - from table0 in my previous SQL example), and then the count field (a6):
CriteriaBuilder cb = em.getCriteriaBuilder(); // Main Query CriteriaQuery cq = cb.createTupleQuery(); Root<EntityTable0> root = cq.from(EntityTable0.class); // Subquery Subquery sub = cq.subquery(Long.class); Root<EntityTable1> subRoot = sub.from(EntityTable1.class); Path<EntityTable2> pathTable2 = root.get("field4"); Predicate conditionSubquery = cb.equal(subRoot.get("field1t1"), pathTable2.get("field1t2")); // Select on the subquery sub.select(cb.count(subRoot)).where(conditionSubquery); // Multi-select on main TupleQuery cq.multiselect(root, sub.getSelection());
When I run the above JPA code, what I get is this:
SELECT t0.field1 AS a1, t0.field2 AS a2, t0.field3 AS a3, t0.field4 AS a4, t0.field5 AS a5, COUNT(t1.id) FROM table0 t0, table2 t2, table1 t1
So basically, the subquery is not executed, and the "condicionSubquery" predicate is not even taken into consideration.
What Im I doing wrong? I tested the SQL and it works as expected.
I'm using EclipseLink 2.7 implementation of JPA.