Search in sources :

Example 1 with SortOptions

use of com.servoy.j2db.query.SortOptions in project servoy-client by Servoy.

the class SQLGenerator method addSorts.

public void addSorts(QuerySelect sqlSelect, BaseQueryTable selectTable, IGlobalValueEntry provider, ITable table, List<SortColumn> orderByFields, boolean includeRelated, boolean permanentJoins) throws RepositoryException {
    List<Column> unusedRowidentColumns = new ArrayList<Column>(table.getRowIdentColumns());
    for (int i = 0; orderByFields != null && i < orderByFields.size(); i++) {
        SortColumn sc = orderByFields.get(i);
        // can be column or aggregate
        IColumn column = sc.getColumn();
        if (column.getDataProviderType() == MEDIA && (column.getFlags() & (IDENT_COLUMNS | UUID_COLUMN)) == 0) {
            // skip cannot sort blob columns
            continue;
        }
        SortOptions sortOptions = application.getFoundSetManager().getSortOptions(sc.getColumn());
        Relation[] relations = sc.getRelations();
        // compare on server objects, relation.foreignServerName may be different in case of duplicates
        boolean doRelatedJoin = (includeRelated && relations != null);
        if (doRelatedJoin) {
            FlattenedSolution fs = application.getFlattenedSolution();
            for (Relation relation : relations) {
                if (relation.isMultiServer() && !fs.getTable(relation.getForeignDataSource()).getServerName().equals(table.getServerName())) {
                    doRelatedJoin = false;
                    break;
                }
            }
        }
        if (doRelatedJoin) // related sort, cannot join across multiple servers
        {
            BaseQueryTable primaryQtable = selectTable;
            BaseQueryTable foreignQtable = null;
            for (Relation relation : relations) {
                // join must be re-created as it is possible to have globals involved;
                // first remove, then create it
                ISQLTableJoin join = (ISQLTableJoin) sqlSelect.getJoin(primaryQtable, relation.getName());
                if (join != null)
                    sqlSelect.getJoins().remove(join);
                if (join == null) {
                    ITable foreignTable = application.getFlattenedSolution().getTable(relation.getForeignDataSource());
                    foreignQtable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
                } else {
                    foreignQtable = join.getForeignTable();
                }
                sqlSelect.addJoin(createJoin(application.getFlattenedSolution(), relation, primaryQtable, foreignQtable, permanentJoins, provider));
                primaryQtable = foreignQtable;
            }
            IQuerySelectValue queryColumn;
            if (column instanceof Column) {
                queryColumn = ((Column) column).queryColumn(foreignQtable);
                unusedRowidentColumns.remove(column);
            } else if (column instanceof AggregateVariable) {
                AggregateVariable aggregate = (AggregateVariable) column;
                queryColumn = new QueryAggregate(aggregate.getType(), new QueryColumn(foreignQtable, -1, aggregate.getColumnNameToAggregate(), aggregate.getDataProviderType(), aggregate.getLength(), 0, null, aggregate.getFlags()), aggregate.getName());
                // there has to be a group-by clause for all selected fields
                List<IQuerySelectValue> columns = sqlSelect.getColumns();
                for (IQuerySelectValue selectVal : columns) {
                    List<IQuerySelectValue> groupBy = sqlSelect.getGroupBy();
                    if (selectVal instanceof QueryColumn && (groupBy == null || !groupBy.contains(selectVal))) {
                        sqlSelect.addGroupBy(selectVal);
                    }
                }
                // if the aggregate has not been selected yet, add it and skip it in the result
                QueryAggregate skippedAggregate = new QueryAggregate(aggregate.getType(), QueryAggregate.ALL, new QueryColumn(foreignQtable, -1, aggregate.getColumnNameToAggregate(), aggregate.getDataProviderType(), aggregate.getLength(), 0, null, aggregate.getFlags()), aggregate.getName(), null, true);
                if (!columns.contains(skippedAggregate)) {
                    sqlSelect.addColumn(skippedAggregate);
                }
            } else {
                // $NON-NLS-1$
                Debug.log("Skipping sort on unexpected related column type " + column.getClass());
                continue;
            }
            sqlSelect.addSort(new QuerySort(queryColumn, sc.getSortOrder() == ASCENDING, sortOptions));
        } else {
            // make sure an invalid sort is not possible
            if (column instanceof Column && column.getTable().getName().equals(table.getName())) {
                sqlSelect.addSort(new QuerySort(((Column) column).queryColumn(selectTable), sc.getSortOrder() == ASCENDING, sortOptions));
                unusedRowidentColumns.remove(column);
            } else {
                // $NON-NLS-1$ //$NON-NLS-2$
                Debug.log("Skipping sort on unrelated column " + column.getName() + '.' + column.getTable().getName() + " for table " + table.getName());
            }
        }
    }
    // Make sure pk is part of the sort, in case of non-unique sort columns, the sorted result may not be the same in each fetch
    if (enforcePkInSort) {
        for (Column column : unusedRowidentColumns) {
            SortOptions sortOptions = application.getFoundSetManager().getSortOptions(column);
            sqlSelect.addSort(new QuerySort(column.queryColumn(selectTable), true, sortOptions));
        }
    }
}
Also used : QueryAggregate(com.servoy.j2db.query.QueryAggregate) ArrayList(java.util.ArrayList) SafeArrayList(com.servoy.j2db.util.SafeArrayList) FlattenedSolution(com.servoy.j2db.FlattenedSolution) AggregateVariable(com.servoy.j2db.persistence.AggregateVariable) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryTable(com.servoy.j2db.query.QueryTable) Relation(com.servoy.j2db.persistence.Relation) IRelation(com.servoy.j2db.persistence.IRelation) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) IColumn(com.servoy.j2db.persistence.IColumn) Column(com.servoy.j2db.persistence.Column) IColumn(com.servoy.j2db.persistence.IColumn) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) QuerySort(com.servoy.j2db.query.QuerySort) IQuerySort(com.servoy.j2db.query.IQuerySort) ITable(com.servoy.j2db.persistence.ITable) List(java.util.List) ArrayList(java.util.ArrayList) SafeArrayList(com.servoy.j2db.util.SafeArrayList) Collectors.toList(java.util.stream.Collectors.toList) SortOptions(com.servoy.j2db.query.SortOptions) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue)

Example 2 with SortOptions

use of com.servoy.j2db.query.SortOptions in project servoy-client by Servoy.

the class DBValueList method createValuelistQuery.

public static QuerySelect createValuelistQuery(IServiceProvider application, ValueList valueList, ITable table) {
    if (table == null)
        return null;
    FoundSetManager foundSetManager = ((FoundSetManager) application.getFoundSetManager());
    // do not add the default pk-sort, only add real configured sort columns on value list
    List<SortColumn> sortColumns = valueList.getSortOptions() == null ? null : foundSetManager.getSortColumns(table, valueList.getSortOptions());
    int showValues = valueList.getShowDataProviders();
    int returnValues = valueList.getReturnDataProviders();
    int total = (showValues | returnValues);
    QuerySelect select = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    ArrayList<IQuerySort> orderColumns = new ArrayList<IQuerySort>();
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    boolean useDefinedSort = sortColumns != null && sortColumns.size() > 0;
    if (useDefinedSort) {
        for (SortColumn sc : sortColumns) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(sc.getColumn());
            orderColumns.add(new QuerySort(getQuerySelectValue(table, select.getTable(), sc.getDataProviderID()), sc.getSortOrder() == SortColumn.ASCENDING, sortoptions));
        }
    }
    if ((total & 1) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID1());
        columns.add(cSQLName);
        if ((showValues & 1) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID1()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    if ((total & 2) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID2());
        columns.add(cSQLName);
        if ((showValues & 2) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID2()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    if ((total & 4) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID3());
        columns.add(cSQLName);
        if ((showValues & 4) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID3()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    // check if we can still use distinct
    select.setDistinct(SQLGenerator.isDistinctAllowed(columns, orderColumns));
    select.setColumns(columns);
    select.setSorts(orderColumns);
    return select;
}
Also used : ArrayList(java.util.ArrayList) SafeArrayList(com.servoy.j2db.util.SafeArrayList) QuerySelect(com.servoy.j2db.query.QuerySelect) IQuerySort(com.servoy.j2db.query.IQuerySort) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryTable(com.servoy.j2db.query.QueryTable) IQuerySort(com.servoy.j2db.query.IQuerySort) QuerySort(com.servoy.j2db.query.QuerySort) SortOptions(com.servoy.j2db.query.SortOptions) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue)

Aggregations

BaseQueryTable (com.servoy.base.query.BaseQueryTable)2 IQuerySelectValue (com.servoy.j2db.query.IQuerySelectValue)2 IQuerySort (com.servoy.j2db.query.IQuerySort)2 QuerySort (com.servoy.j2db.query.QuerySort)2 QueryTable (com.servoy.j2db.query.QueryTable)2 SortOptions (com.servoy.j2db.query.SortOptions)2 SafeArrayList (com.servoy.j2db.util.SafeArrayList)2 ArrayList (java.util.ArrayList)2 BaseQueryColumn (com.servoy.base.query.BaseQueryColumn)1 FlattenedSolution (com.servoy.j2db.FlattenedSolution)1 AggregateVariable (com.servoy.j2db.persistence.AggregateVariable)1 Column (com.servoy.j2db.persistence.Column)1 IColumn (com.servoy.j2db.persistence.IColumn)1 IRelation (com.servoy.j2db.persistence.IRelation)1 ITable (com.servoy.j2db.persistence.ITable)1 Relation (com.servoy.j2db.persistence.Relation)1 ISQLTableJoin (com.servoy.j2db.query.ISQLTableJoin)1 QueryAggregate (com.servoy.j2db.query.QueryAggregate)1 QueryColumn (com.servoy.j2db.query.QueryColumn)1 QuerySelect (com.servoy.j2db.query.QuerySelect)1