Referencing outer criteria query aliases from within an SQLProjection

Question

I am aware that you can use {alias} to refer to the root entity within an SQLProjection:

Projections.sqlProjection("MIN({alias}.field) as value", new String[]{"value"}, new Type[]{new LongType()}))

What I am trying to do is to reference an alias for a non-root entity:

Projections.sqlProjection("MIN(i.powerRestarts) as value", new String[]{"value"}, new Type[]{new LongType()}))

where i is an alias from the outer criteria query. The code above throws an SQL exception saying that i.powerRestarts cannot be found.

Is it possible to refer to a non-root alias from an SQLProjection?

score 15 · Accepted Answer · edited Feb 10 '17 at 11:30

Having done some googling, it appears that this this is not possible - Hibernate only allows inclusion of the root entity alias using {alias} in the SQL string of the SQLProjection. I did however find this issue regarding the limitation on the Hibernate JIRA pages.

Someone has kindly submitted a patch that allows the use of non-root aliases in the SQLProjection string, through a new RestrictionsExt class. Using my example from the question:

Projections.sqlProjection("MIN(i.powerRestarts) as value", new String[]{"value"}, new Type[]{new LongType()}))

The alias i can now be referenced as:

RestrictionsExt.sqlProjection("MIN({i}.powerRestarts) as value", "value", new LongType())

I had to modify the static RestrictionsExt.sqlProjection method to allow specification of the type for the column alias ("value") (here defined as LongType), as the patch didn't allow this and defaulted to StringType.

The SQLAliasedProjection class in the patch also requires access to the following private methods in org.hibernate.loader.criteria.CriteriaQueryTranslator: getOuterQueryTranslator and getAliasedCriteria. To get this to work without modifying the Hibernate source, I used reflection:

cri = ((org.hibernate.loader.criteria.CriteriaQueryTranslator) criteriaQuery).getAliasedCriteria(alias);

was changed to:

Method m = ((org.hibernate.loader.criteria.CriteriaQueryTranslator) criteriaQuery).getClass().getDeclaredMethod("getAliasedCriteria", String.class);
m.setAccessible(true);
cri = (Criteria) m.invoke(((org.hibernate.loader.criteria.CriteriaQueryTranslator) criteriaQuery), alias);

Hopefully this will help anyone else facing the same problem.

Referencing outer criteria query aliases from within an SQLProjection

1 Answers1