0 Replies Latest reply on Jul 19, 2016 4:21 AM by hamideh_p

    Problem with jpa criteria query multiselect

    hamideh_p

      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.