Here's the criteria creation:
Code:
Criteria criteria = hibernateSession.createCriteria(Post.class);
criteria.createAlias("createdBy", "createdBy");
criteria.createAlias("type", "type");
Object[] types =
{ new Long(Type.GENERAL_STAFF), new Long(Type.POSSIBLE_SOLUTION), new Long(Type.SOLUTION) };
criteria.add(Restrictions.in("type.id", types));
criteria.addOrder(Order.asc("type.id"));
ProjectionList projections = Projections.projectionList();
projections.add(Projections.count("id"));
projections.add(Projections.groupProperty("createdBy.lastName"));
projections.add(Projections.groupProperty("type.name"));
criteria.setProjection(projections);
Here's the generated SQL:
Code:
select count(this_.id) as y0_, createdby1_.lastName as y1_, type2_.name as y2_
from troubletickets_posts this_ inner join troubletickets_types type2_ on this_.trty_id=type2_.id inner join users createdby1_ on this_.created_usr_id=createdby1_.id
where type2_.id in (3, 4, 5)
group by createdby1_.lastName, type2_.name
order by type2_.id asc
Which throws the following sql error:
Quote:
2005-03-14 14:52:17,990 WARN (org.hibernate.util.JDBCExceptionReporter:57) - SQL Error: 0, SQLState: 42803
2005-03-14 14:52:17,990 ERROR (org.hibernate.util.JDBCExceptionReporter:58) - ERROR: column "type2_.id" must appear in the GROUP BY clause or be used in an aggregate function
Essentially, this means that, due to the order by, the type.id needs to be in the group-by field. As I understand it, the Criteria class is supposed to take care of the group-by, right? I don't know, maybe this isn't a supported case. It was easy enough to work around by getting rid of the alias, and just returning the whole Type object.
Login
FAQ
Search