how do I group by a property of a related entity in a criteria?

Question

Hi.

I'm writing a criteria that should group the results by a property of a related entity. I've tried using an alias, tried using the property path itself, but so far I get nothing. say my classes are (rough sketch):

class A{
 @ManyToOne(...)
 B b;
}
class B{
 @OneToOne(...)
 C c;
}
class C{
 String s;
}

and I want a criteria that returns the amount of A's and B's for each unique string s in C.

my initial attempt was:

session.createCriteria(A.class)
  .setProjection(Projections.projectionList()
    .add(Projections.groupProperty("b.c.s"), "string")
    .add(Projections.countDistinct("b"), "b's")
    .add(Projections.rowCount(), "a's"))

This didn't help much as b.c.s is not a property of A.

then I tried

session.createCriteria(A.class)
  .createAlias("b.c", "al")
  .setProjection(Projections.projectionList()
    .add(Projections.groupProperty("al.s"), "string")
    .add(Projections.countDistinct("b"), "b's")
    .add(Projections.rowCount(), "a's"))

this actually was translated to SQL, but did not get far, as it did not include any joins in the query.

seems I am doing something wrong here.

Is it possible to get an efficient query of this kind using the criteria API?

Answer 1

maybe something like this:

sess.createCriteria(A.class)
    .createCriteria("b")
    .createCriteria("c")
    .setProjection(Projections.projectionList()
    .add(Projections.groupProperty("s"), "string")
    .list()
    .size()

i think you must look in double createCriteria()