Examples with QueryAggregate com.servoy.j2db.query.QueryAggregate used on opensource projects

Example 1 with QueryAggregate

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

the class QBResult method getColumns.

/**
 * returns an array with all the columns that will be in the select of this query.
 * can return empty array. Then the system will auto append the pk when this query is used.
 * @sample
 * var columns = query.result.getColumns();
 *
 * @return An array of QBColumn thats in the select of this query.
 */
@JSFunction
public QBColumn[] getColumns() {
    ArrayList<IQuerySelectValue> columns = getParent().getQuery().getColumns();
    QBColumn[] result = new QBColumn[columns == null ? 0 : columns.size()];
    for (int i = 0; i < result.length; i++) {
        IQuerySelectValue selectValue = columns.get(i);
        if (selectValue instanceof QueryColumn) {
            result[i] = new QBColumn(getRoot(), getParent(), selectValue);
        } else if (selectValue instanceof QueryAggregate) {
            result[i] = new QBAggregate(getRoot(), getParent(), selectValue, ((QueryAggregate) selectValue).getType(), ((QueryAggregate) selectValue).getQuantifier());
        } else if (selectValue instanceof QueryFunction) {
            result[i] = new QBFunction(getRoot(), getParent(), ((QueryFunction) selectValue).getFunction(), ((QueryFunction) selectValue).getArgs());
        }
    }
    return result;
}

Example 2 with QueryAggregate

use of com.servoy.j2db.query.QueryAggregate 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));
        }
    }
}

Example 3 with QueryAggregate

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

the class SQLGenerator method createAggregates.

private void createAggregates(SQLSheet sheet, QueryTable queryTable) throws RepositoryException {
    Table table = sheet.getTable();
    Iterator<AggregateVariable> it = application.getFlattenedSolution().getAggregateVariables(table, false);
    while (it.hasNext()) {
        AggregateVariable aggregate = it.next();
        QuerySelect sql = new QuerySelect(queryTable);
        sql.addColumn(new QueryAggregate(aggregate.getType(), new QueryColumn(queryTable, -1, aggregate.getColumnNameToAggregate(), aggregate.getDataProviderType(), aggregate.getLength(), 0, null, aggregate.getFlags()), aggregate.getName()));
        sheet.addAggregate(aggregate.getDataProviderID(), aggregate.getDataProviderIDToAggregate(), sql);
    }
}

Example 4 with QueryAggregate

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

the class FoundSetManager method getTableCount.

public int getTableCount(ITable table) {
    if (table != null) {
        try {
            long time = System.currentTimeMillis();
            IDataServer ds = application.getDataServer();
            String transaction_id = getTransactionID(table.getServerName());
            QuerySelect countSelect = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
            // $NON-NLS-1$
            countSelect.addColumn(new QueryAggregate(QueryAggregate.COUNT, new QueryColumnValue(Integer.valueOf(1), "n", true), null));
            IDataSet set = ds.performQuery(application.getClientID(), table.getServerName(), transaction_id, countSelect, null, getTableFilterParams(table.getServerName(), countSelect), false, 0, 10, IDataServer.FOUNDSET_LOAD_QUERY);
            if (Debug.tracing()) {
                Debug.trace(// $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
                "Table count time: " + (System.currentTimeMillis() - time) + " thread: " + Thread.currentThread().getName() + ", SQL: " + countSelect.toString());
            }
            if (set.getRowCount() > 0) {
                Object[] row = set.getRow(0);
                if (row.length > 0) {
                    return Utils.getAsInteger(row[0]);
                }
            }
        } catch (Exception e) {
            Debug.error(e);
        }
    }
    return -1;
}

Example 5 with QueryAggregate

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

the class SQLGenerator method createAggregateSelect.

/**
 * Create the sql for a single aggregate on a column.
 *
 * @param aggregee
 * @return
 */
public static QuerySelect createAggregateSelect(int aggregateType, ITable table, Object aggregee) {
    Column column = null;
    if (aggregee instanceof Column) {
        column = (Column) aggregee;
    }
    QuerySelect select = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    select.addColumn(new QueryAggregate(aggregateType, // $NON-NLS-1$
    (column == null) ? // $NON-NLS-1$
    (IQuerySelectValue) new QueryColumnValue(aggregee, "n", aggregee instanceof Integer || QueryAggregate.ASTERIX.equals(aggregee)) : column.queryColumn(select.getTable()), // $NON-NLS-1$
    "maxval"));
    return select;
}