How to convert a SQL query with a subquery and rely on the JPA criteria builder

Question

I am trying to convert this SQL query to use the JPA criteria builder. The answer should be double or floating.

SELECT CAST((COUNT(m.id) -
(SELECT COUNT(s.id) 
  FROM mobile_unit as s
  left JOIN incident as i ON s.incidentId=i.id
  JOIN organizational_unit as o ON s.organizationalUnitId=o.id
  WHERE (s.organizationalUnitId = 1 AND s.incidentId IS NULL))) AS float) 
/COUNT(m.id) 
FROM mobile_unit as m 
JOIN organizational_unit as o ON m.organizationalUnitId=o.id
WHERE m.organizationalUnitId = 1

Answer 1

Without checking this, here is an example of how this can be done.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Float> cq = cb.createQuery(Float.class);

Root<MobileUnit> root = cq.from(MobileUnit.class);
// Join is actually unnecessary
root.join("organizationalUnit", JoinType.INNER);
cq.where(cb.equal(
    root.get("organizationalUnitId"),
    1
));

Subquery<Long> subquery = cq.subquery(Long.class);
Root<MobileUnit> subRoot = subquery.from(MobileUnit.class);
// Actually these joins are unnecessary, but you requested them..
subRoot.join("incident", JoinType.LEFT);
subRoot.join("organizationalUnit", JoinType.INNER);

subquery.select(cb.count(subRoot.get("id")));
subquery.where(cb.and(
    subRoot.get("organizationalUnitId").eq(cb.literal(1)),
    subRoot.get("incidentId").isNull()
));

cq.select(cb.quot(
    cb.diff(
        cb.count(root.get("id")),
        subquery
    ).as(Float.class),
    cb.count(root.get("id"))
));