Application: gvSIG desktop

import org.gvsig.expressionevaluator.Code;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_LET;

import org.gvsig.expressionevaluator.ExpressionUtils;

        if (this.query != null && this.query.hasGroupByColumns()) {

            JDBCSQLBuilderBase subsqlbuilder = this.createSQLBuilder();

            SelectBuilder subselect = subsqlbuilder.select();

            subselect.column().value(subsqlbuilder.count().all());

            subselect.from().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

                    .name(this.table.getTable());

            subselect.from().subquery(this.table.getSubquery());

            Evaluator filter = query == null ? null : query.getFilter();

            if (filter != null) {

                String sqlfilter = filter.getSQL();

                if (!StringUtils.isEmpty(sqlfilter)) {

                    if (this.helper.supportFilter(this.featureType, filter)) {

                        subselect.where().set(expbuilder.toValue(sqlfilter));

                    }

                }

            if (!StringUtils.isEmpty(baseFilter)) {

                subselect.where().and(expbuilder.toValue(baseFilter));

            }

            FeatureQueryOrder order = query == null ? null : query.getOrder();

            if (order != null) {

                for (FeatureQueryOrder.FeatureQueryOrderMember member : order.members()) {

                    if (member.hasEvaluator()) {

                        String sqlorder = member.getEvaluator().getSQL();

                        if (!StringUtils.isEmpty(sqlorder)) {

                            subselect.order_by().custom(sqlorder);

                        }

                    } else {

                        subselect.order_by()

                                .column(member.getAttributeName())

                                .ascending(member.getAscending());

                    }

                }

            }

            List<String> groupbyColumns = query == null ? null : query.getGroupByColumns();

            if (groupbyColumns != null && !groupbyColumns.isEmpty()) {

                for (String columnName : groupbyColumns) {

                    if (this.featureType.getAttributeDescriptor(columnName) != null) {

                        subselect.group_by(expbuilder.column(columnName));

                    } else {

                        try {

                            try {

                                Code groupByColumnCode = ExpressionUtils.compile(columnName);

                                if (groupByColumnCode.code() == Code.CALLABLE) {

                                    Code.Callable callable = (Code.Callable) groupByColumnCode;

                                    if (callable.name().equalsIgnoreCase(FUNCTION_LET)) {

                                        Code exp = callable.parameters().get(1);

                                        Code name = callable.parameters().get(0);

                                        subselect.column().value(exp.toValue())

                                                .as((String) ((Code.Constant) name).value());

                                        // nombre que se pone en la parte del groupby debe de ser el nombre de la var del set

                                        groupByColumnCode = exp; 

                                    }

                                }

                                subselect.group_by(groupByColumnCode.toValue());

                            } catch (Exception ex) {

                                throw new RuntimeException("Not able to create column by expression in groupby query", ex);

                            }

                        } catch (Exception ex) {

                            throw new RuntimeException("Not able to create column by expression in groupby query", ex);

                        }

                    }

                }

            }

            this.helper.processSpecialFunctions(subsqlbuilder, featureType, null);

            subsqlbuilder.setProperties(

                    ExpressionBuilder.Variable.class,

                    PROP_TABLE, table

            );

            String subsql = subselect.toString();

            select.from().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

                    .name(this.table.getTable());

            select.from().subquery(subsql);

            select.column().value(sqlbuilder.count().all());

            select.from().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

                    .name(this.table.getTable());

            select.from().subquery(this.table.getSubquery());

            if (!StringUtils.isEmpty(baseFilter)) {

                sqlbuilder.select().where().set(expbuilder.custom(baseFilter));

            if (this.query != null) {

                if (this.query.getFilter() != null && !StringUtils.isBlank(this.query.getFilter().getSQL())) {

                    // El and() hace un set() si no hay un filtro previo

                    select.where().and(expbuilder.toValue(this.query.getFilter().getSQL()));

                }

            }

                ExpressionBuilder.Variable.class,