Grails Custom Projection | Projections Property | Custom Projection Property

Grails Custom Projection | Projections Property | Custom Projection Property

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")
    }
}