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));
}
}
}
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;
}
Aggregations