1

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.

JMart
  • 191
  • 3
  • 8
  • 1
    I got the same error with the same EclipseLink version. Oddly enough, if I write this in JPQL (in your case `select et0, (select count(et1) from EntityTable1 et1 where et1.field1t1 = et0.field1t2) from EntityTable0 et0` _I hope I got your relations right_) it works. – Filou Aug 27 '21 at 06:26

0 Answers0