title: how to use subquery with projection using criteria in hibernate
body: <p>I have written a query in sql which is like...</p>
  
  <pre><code>SELECT c.clientfirstname, 
         c.clientlastname, 
         c.clientage, 
         c.status, 
         Sum(i.annualamount) AS amount, 
         p.planname, 
         c.clientid 
  FROM   client c, 
         income i, 
         plan p 
  WHERE  c.clientid = i.clientid 
         AND planid = (SELECT Max(p.planid) 
                       FROM   plan p 
                       WHERE  c.clientid = p.clientid 
                       GROUP  BY p.clientid) 
         AND ( c.clientfirstname LIKE 'rahul' ) 
  GROUP  BY i.clientid 
  </code></pre>
  
  <p>this query is fine and working properly, but i want to use criteria query of hibernate in place of above SQL query because i am using hibernate in my application.
  i am getting problem while adding condition like <code>planId=(SELECT MAX(p.planId) FROM plan p WHERE c.clientId=p.clientId GROUP BY p.clientId)</code> so how to write above sql query in hibernate criteria. i tried with this query</p>
  
  <pre><code>DetachedCriteria dc = DetachedCriteria.forClass(Client.class);
      dc.createAlias("plans", "pla");
      ProjectionList proj = Projections.projectionList();
      proj.add(Projections.max("pla.planId"));
          proj.add(Projections.groupProperty("clientId"));
      dc.setProjection(proj);
  
  
      Criteria criteria =      sessionFactory.getCurrentSession().createCriteria(Client.class);
      criteria.createAlias("incoms", "inco");
      criteria.createAlias("plans", "plan");
  
      Criterion firstname = Restrictions.like("clientFirstName", searchValue+"%");
  
      criteria.setProjection(
              Projections.projectionList()            
              .add(Projections.property("clientFirstName"))
              .add(Projections.property("clientLastName"))
              .add(Projections.property("clientAge"))
              .add(Projections.property("status"))
              .add(Projections.sum("inco.annualAmount"))
                  .add(Projections.property("plan.planName"))
              .add(Projections.groupProperty("clientId"))
              );
  
      criteria.add(Subqueries.propertyEq("plan.planId", dc));
  
      Disjunction disjunction = Restrictions.disjunction();
  
      disjunction.add(firstname);
  
      criteria.add(disjunction);
  
      return criteria.list();
  }
  </code></pre>
  
  <p>but i am getting error like this.....</p>
  
  <pre><code>Hibernate: 
  select this_.clientFirstName as y0_, this_.clientLastName as y1_,
    this_.clientAge as y2_, this_.status as y3_, sum(inco1_.annualAmount) as y4_,
    plan2_.planName as y5_, this_.clientId as y6_ 
  from Client this_ inner join Income inco1_ on this_.clientId=inco1_.clientId
    inner join Plan plan2_ on this_.clientId=plan2_.clientId 
  where plan2_.planId = 
      (select max(pla1_.planId) as y0_, this_.clientId as y1_ 
       from Client this_ inner join Plan pla1_ on this_.clientId=pla1_.clientId
       group by this_.clientId
      ) 
    and (this_.clientFirstName like ? 
    or this_.clientLastName like ? 
    or this_.clientPhoneNo like ? 
    or this_.clientEmail like ?)
  group by this_.clientId
  </code></pre>
  
  <p>Error:could not execute query</p>
  
  <p>there are three tables income plan and client, client id is the foreign key in both plan and income table, and in plan table there are many planname available for one client id and i want to fetch last plan group by clientId from plan table...</p>
  
  <p>my above (on top) SQL query is fine so i want same query using hibernate criteria. </p>