0 Replies Latest reply on Sep 4, 2006 6:02 AM by Muhammad Shouab

    Aggregate function in where clause (Criteria API)

    Muhammad Shouab Newbie

      I am using Criteria API. I want to use the Aggregate Functions like
      Avg(),count() etc in the where clause i know its available through Projections but projections used as the slect part of the query i want to use these functions in where clause like
      criteria.add(Restriction.eq(Projections.count("emp"),5));

      Is there any way to do that.

      if the problem is not clear lets say i have two tables Employee and Address and the relation between is one to many Employee have multiple Addresses so i want to retrieve the employee who have 5 or more Addresses.

      Code is like that.

      Criteria criteria = session.createCriteria(Employee.class,"emp");
       criteria.setFetchMode("addresses",FetchMode.EAGER);
      ProjectionList projList = Projections.projectionList();
      projList.add(Projections.count("emp.addresses"),"NoOfOpp");
      criteria.setProjection(projList);
      criteria.add((Restrictions.ge("NoOfOpp",6)));
      List list=criteria.list();
      
      


      When i execute this it gives me NoOfOpp invalid identifier is any other way round to accomplish the task.