Grails Custom Projection | Projections Property | Custom Projection Property
You have to create a groovy file as below:
You have to attach this projection property as below:
And finally you can use the custom projection as below:
You have to create a groovy file as below:
import org.hibernate.Criteria import org.hibernate.HibernateException import org.hibernate.criterion.CriteriaQuery import org.hibernate.criterion.SimpleProjection import org.hibernate.dialect.function.SQLFunction import org.hibernate.type.Type /** * Created by pritom on 25/09/2017. */ class HomeChildCount extends SimpleProjection { protected final String propertyName private final String functionName HomeChildCount() { this("sum", "id") } HomeChildCount(String functionName, String propertyName) { this.functionName = functionName this.propertyName = propertyName } public String getFunctionName() { return this.functionName } public String getPropertyName() { return this.propertyName } public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { Type type = this.getFunction(criteriaQuery).getReturnType(criteriaQuery.getType(criteria, this.getPropertyName()), criteriaQuery.getFactory()) return [type] as Type[] } public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery) throws HibernateException { String functionFragment = this.getFunction(criteriaQuery).render(criteriaQuery.getType(criteria, this.getPropertyName()), this.buildFunctionParameterList(criteria, criteriaQuery), criteriaQuery.getFactory()) //return functionFragment + " as y" + loc + '_' return "(select count(c.id) from child c where c.home_id=this_.id) as y" + loc + "_" } protected SQLFunction getFunction(CriteriaQuery criteriaQuery) { return this.getFunction(this.getFunctionName(), criteriaQuery) } protected SQLFunction getFunction(String functionName, CriteriaQuery criteriaQuery) { SQLFunction function = criteriaQuery.getFactory().getSqlFunctionRegistry().findSQLFunction(functionName) if(function == null) { throw new HibernateException("Unable to locate mapping for function named [" + functionName + "]") } else { return function } } protected List buildFunctionParameterList(Criteria criteria, CriteriaQuery criteriaQuery) { return this.buildFunctionParameterList(criteriaQuery.getColumn(criteria, this.getPropertyName())) } protected List buildFunctionParameterList(String column) { return Collections.singletonList(column) } public String toString() { return this.functionName + "(" + this.propertyName + ')' } }
You have to attach this projection property as below:
Object.metaClass.with { activeChildCount = { String alias -> delegate.delegate.addProjectionToList(new HomeChildCount(), alias) } }
And finally you can use the custom projection as below:
Home.createCriteria().list { resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) projections { groupProperty("id", "id") groupProperty("name", "name") groupProperty("roll", "roll") groupProperty("departmentName", "departmentName") activeChildCount("child_count") } }