Hi everyone,
I've got this problem where I have to use criteria query for creating application reports,but I cant's get parts of the query work properly,my equivalent native query is as follows:
select t.organizationid
,(select count(*) from tb_registration d where d.requesttype_id=1 and d.organizationid=t.organizationid ) -- i'm having problem adding this statement to the criteria query
,(select count(d.requesttype_id) from tb_registration d where d.requesttype_id=4 and d.organizationid=t.organizationid) -- i'm having problem adding this statement to to the criteria query
,count(t.requesttype_id)
from tb_registration t
group by t.organizationid;
My criteria query is like this :
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<Registration> registration = cq.from(Registration.class);
EntityType<Registration> Registration_ = registration .getModel();
/*adding some predicates*/
cq.where(predicates.toArray(new Predicate[]{}));
/*this is where I'm not sure of*/
CriteriaQuery q = cb.createQuery();
Root<Registration> c = q.from(Registration.class);
q.select(cb.countDistinct(c));
q.where(cb.equal(c.get("requestType").get("requestTypeId"),1),
cb.equal(c.get("organizationId"),registration.get("organizationId")));
cq.multiselect(registration .get("organizationId"), cb.count(registration .get("requestType")));
List<Expression> list = new ArrayList();
list.add(registration .get("organizationId"));
cq.groupBy(list);
return cq;
somehow i want to add the result of the "q" query part to the multiselect statement in line 14,but I haven't been able to do so.
I'd appreciate it if anyone could help me.