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

Example 1 with ExistsCondition

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

the class SQLGenerator method createExistsCondition.

public static ISQLCondition createExistsCondition(IDataProviderHandler flattenedSolution, QuerySelect sqlSelect, ISQLCondition condition, List<IRelation> relations, BaseQueryTable columnTable, IGlobalValueEntry provider, BaseQueryColumn[] pkQueryColumns) throws RepositoryException {
    // exists (select 1 from innermain join related1 ... join relatedn where innermain.pk = main.pk having aggregate(relatedn))
    if (relations.size() == 0) {
        // searching for aggregate in main table, does no make sense.. ignore in search
        return null;
    }
    QuerySelect existsSelect = new QuerySelect(new QueryTable(sqlSelect.getTable().getName(), sqlSelect.getTable().getDataSource(), sqlSelect.getTable().getCatalogName(), sqlSelect.getTable().getSchemaName()));
    existsSelect.addColumn(new QueryColumnValue(Integer.valueOf(1), null, true));
    // innermain.pk = main.pk
    QueryColumn[] innerPkColumns = new QueryColumn[pkQueryColumns.length];
    for (int p = 0; p < pkQueryColumns.length; p++) {
        BaseQueryColumn pk = pkQueryColumns[p];
        innerPkColumns[p] = new QueryColumn(existsSelect.getTable(), pk.getId(), pk.getName(), pk.getColumnType().getSqlType(), pk.getColumnType().getLength(), pk.getColumnType().getScale(), pk.getNativeTypename(), pk.getFlags(), pk.isIdentity());
        // group by on the inner pk, some dbs (hxtt dbf) require that
        existsSelect.addGroupBy(innerPkColumns[p]);
    }
    existsSelect.addCondition("AGGREGATE-SEARCH", new // $NON-NLS-1$
    SetCondition(// $NON-NLS-1$
    new int[] { IBaseSQLCondition.EQUALS_OPERATOR }, // $NON-NLS-1$
    innerPkColumns, pkQueryColumns, true));
    // add the joins
    BaseQueryTable prevTable = existsSelect.getTable();
    for (IRelation relation : relations) {
        ITable foreignTable = flattenedSolution.getTable(relation.getForeignDataSource());
        QueryTable foreignQtable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
        existsSelect.addJoin(createJoin(flattenedSolution, relation, prevTable, foreignQtable, true, provider));
        prevTable = foreignQtable;
    }
    existsSelect.addHaving(AbstractBaseQuery.relinkTable(columnTable, prevTable, condition));
    return new ExistsCondition(existsSelect, true);
}

Example 2 with ExistsCondition

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

the class SQLGenerator method createAggregateSelect.

public static QuerySelect createAggregateSelect(QuerySelect sqlSelect, Collection<QuerySelect> aggregates, List<Column> pkColumns) {
    QuerySelect selectClone = deepClone(sqlSelect);
    selectClone.clearSorts();
    selectClone.setDistinct(false);
    selectClone.setColumns(null);
    selectClone.removeUnusedJoins(true);
    QuerySelect aggregateSqlSelect;
    if (selectClone.getJoins() == null) {
        // simple case, no joins
        // Select count(pk) from main where <condition>
        aggregateSqlSelect = selectClone;
    } else {
        // we have joins, change to an exists-query to make the aggregates correct, otherwise duplicate records
        // in the main table cause incorrect aggregate values
        // Select count(pk) from main main1 where exists (select 1 from main main2 join detail on detail.FK = main2.FK where main1.PK = main2.PK and <condition>)
        QuerySelect innerSelect = selectClone;
        ArrayList<IQuerySelectValue> innerColumns = new ArrayList<IQuerySelectValue>();
        innerColumns.add(new QueryColumnValue(Integer.valueOf(1), null, true));
        innerSelect.setColumns(innerColumns);
        BaseQueryTable innerTable = innerSelect.getTable();
        QueryTable outerTable = new QueryTable(innerTable.getName(), innerTable.getDataSource(), innerTable.getCatalogName(), innerTable.getSchemaName());
        aggregateSqlSelect = new QuerySelect(outerTable);
        for (Column column : pkColumns) {
            // $NON-NLS-1$
            innerSelect.addCondition(// $NON-NLS-1$
            "EXISTS", new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, column.queryColumn(innerTable), column.queryColumn(outerTable)));
        }
        // $NON-NLS-1$
        aggregateSqlSelect.addCondition("EXISTS", new ExistsCondition(innerSelect, true));
    }
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    for (QuerySelect aggregate : aggregates) {
        columns.addAll(AbstractBaseQuery.relinkTable(aggregate.getTable(), aggregateSqlSelect.getTable(), aggregate.getColumnsClone()));
    }
    aggregateSqlSelect.setColumns(columns);
    return aggregateSqlSelect;
}