Add a Group by to a Hibernate Criteria-Query without Projection

Question

I have a Criteria-Query, which joins a second table B, to select entities from table A. The problem is, that this query returns some entities from table A multiple times. But I need the results to be distinct.

Using Criteria.DISTINCT_ROOT_ENTITY is useless, becaus this filters out the multiple occurences after the SQL-Query was executed. So, when I limit my results to 20 hits, I end up with only 4, though there are more entries, that match my query.

In pure SQL I simply can add a "GROUP BY ID" to the query and everything is fine, because the join of table B is only used, to select the entities from table A. But with the Criteria-API I cannot do this. The only way to add a "GROUP BY" is by using Projections. But then, I end up with scalar values, not with a real instance of my class. Using a SQL-restriction does not work either, because hibernate adds a bogous "1=1" after my "GROUP BY"-clause. :(

Any ideas?

Answer 1

Have you tried to use something like this?

    ICriteria criteria = dc.GetExecutableCriteria(RepositoryInstance.Session)
            .SetProjection(Projections.distinct(Projections.projectionList()
                    .add(Projections.property("Prop1"), "Prop1")
                    .add(Projections.property("Prop2"), "Prop2")
                    .add(Projections.property("Prop3"), "Prop3")
                    .add(Projections.property("Prop4"), "Prop4")));
    result = criteria.List();

You can dynamically add properties through reflection of the class.

This creates SQl like this: select distinct prop1,prop2,prop3,prop4 from yourClass

I did not include DetachedCriteria dc since that is irrelevant.