Search in sources :

Example 1 with QueryFilter

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

the class FoundSet method addFilterconditions.

private QuerySelect addFilterconditions(QuerySelect select, List<TableFilter> filters) {
    for (TableFilter tf : iterate(filters)) {
        QueryFilter filtercondition = SQLGenerator.createTableFiltercondition(select.getTable(), sheet.getTable(), tf);
        select.addCondition(SQLGenerator.CONDITION_FILTER, filtercondition.getCondition());
        for (ISQLJoin join : iterate(filtercondition.getJoins())) {
            join.setOrigin(tf);
            select.addJoin(join);
        }
    }
    return select;
}
Also used : QueryFilter(com.servoy.j2db.query.QueryFilter) ISQLJoin(com.servoy.j2db.query.ISQLJoin)

Example 2 with QueryFilter

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

the class SQLGenerator method createTableFiltercondition.

private static QueryFilter createTableFiltercondition(BaseQueryTable qTable, Table table, QuerySelect filterQuery) {
    QuerySelect filterQueryClone = deepClone(filterQuery);
    filterQueryClone.relinkTable(filterQueryClone.getTable(), qTable);
    List<QueryColumn> pkColumns = table.getRowIdentColumns().stream().map(column -> column.queryColumn(qTable)).collect(toList());
    return new QueryFilter(filterQueryClone.getJoins(), pkColumns, filterQueryClone.getWhere());
}
Also used : Array(java.lang.reflect.Array) QuerySort(com.servoy.j2db.query.QuerySort) QueryFunctionType.upper(com.servoy.j2db.query.QueryFunction.QueryFunctionType.upper) CompareCondition(com.servoy.j2db.query.CompareCondition) QueryColumn(com.servoy.j2db.query.QueryColumn) Debug(com.servoy.j2db.util.Debug) IQueryConstants(com.servoy.base.query.IQueryConstants) BaseQueryTable(com.servoy.base.query.BaseQueryTable) SetCondition(com.servoy.j2db.query.SetCondition) QueryTable(com.servoy.j2db.query.QueryTable) Utils(com.servoy.j2db.util.Utils) AbstractBase(com.servoy.j2db.persistence.AbstractBase) AggregateVariable(com.servoy.j2db.persistence.AggregateVariable) IServiceProvider(com.servoy.j2db.IServiceProvider) Map(java.util.Map) IBaseSQLCondition(com.servoy.base.query.IBaseSQLCondition) IPlaceholderKey(com.servoy.j2db.query.IPlaceholderKey) OrCondition(com.servoy.j2db.query.OrCondition) ISQLJoin(com.servoy.j2db.query.ISQLJoin) AbstractBaseQuery(com.servoy.j2db.query.AbstractBaseQuery) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) Collection(java.util.Collection) QueryDelete(com.servoy.j2db.query.QueryDelete) IDENT_COLUMNS(com.servoy.base.persistence.IBaseColumn.IDENT_COLUMNS) IVisitor(com.servoy.j2db.util.visitor.IVisitor) ConverterInfo(com.servoy.j2db.dataprocessing.SQLSheet.ConverterInfo) ISQLCondition(com.servoy.j2db.query.ISQLCondition) ITable(com.servoy.j2db.persistence.ITable) QueryFunction(com.servoy.j2db.query.QueryFunction) IDataProvider(com.servoy.j2db.persistence.IDataProvider) Table(com.servoy.j2db.persistence.Table) ParsedFormat(com.servoy.j2db.util.FormatParser.ParsedFormat) IApplication(com.servoy.j2db.IApplication) Wrapper(org.mozilla.javascript.Wrapper) ASCENDING(com.servoy.j2db.dataprocessing.SortColumn.ASCENDING) List(java.util.List) ColumnType(com.servoy.j2db.query.ColumnType) QueryColumnValue(com.servoy.j2db.query.QueryColumnValue) QueryJoin(com.servoy.j2db.query.QueryJoin) Types(java.sql.Types) IValueConverter(com.servoy.base.dataprocessing.IValueConverter) QueryInsert(com.servoy.j2db.query.QueryInsert) UUID_COLUMN(com.servoy.base.persistence.IBaseColumn.UUID_COLUMN) IQuerySort(com.servoy.j2db.query.IQuerySort) SortOptions(com.servoy.j2db.query.SortOptions) Placeholder(com.servoy.j2db.query.Placeholder) IDataProviderHandler(com.servoy.j2db.persistence.IDataProviderHandler) ScriptCalculation(com.servoy.j2db.persistence.ScriptCalculation) FlattenedSolution(com.servoy.j2db.FlattenedSolution) ObjectPlaceholderKey(com.servoy.j2db.query.ObjectPlaceholderKey) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue) HashMap(java.util.HashMap) IColumn(com.servoy.j2db.persistence.IColumn) ArrayList(java.util.ArrayList) LinkedHashMap(java.util.LinkedHashMap) QueryFilter(com.servoy.j2db.query.QueryFilter) SafeArrayList(com.servoy.j2db.util.SafeArrayList) ISQLSelect(com.servoy.j2db.query.ISQLSelect) Relation(com.servoy.j2db.persistence.Relation) QueryFactory(com.servoy.j2db.query.QueryFactory) ServoyException(com.servoy.j2db.util.ServoyException) QueryCustomSelect(com.servoy.j2db.query.QueryCustomSelect) BaseColumnType(com.servoy.base.query.BaseColumnType) ITypeConverter(com.servoy.base.dataprocessing.ITypeConverter) QueryUpdate(com.servoy.j2db.query.QueryUpdate) RelatedFindState(com.servoy.j2db.dataprocessing.FindState.RelatedFindState) QueryAggregate(com.servoy.j2db.query.QueryAggregate) Iterator(java.util.Iterator) BaseSQLGenerator(com.servoy.base.dataprocessing.BaseSQLGenerator) IColumnTypes(com.servoy.j2db.persistence.IColumnTypes) Column.mapToDefaultType(com.servoy.j2db.persistence.Column.mapToDefaultType) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) ExistsCondition(com.servoy.j2db.query.ExistsCondition) QuerySelect(com.servoy.j2db.query.QuerySelect) AndCondition(com.servoy.j2db.query.AndCondition) Collectors.toList(java.util.stream.Collectors.toList) QueryFunctionType.castfrom(com.servoy.j2db.query.QueryFunction.QueryFunctionType.castfrom) IRelation(com.servoy.j2db.persistence.IRelation) ColumnInfo(com.servoy.j2db.persistence.ColumnInfo) LiteralDataprovider(com.servoy.j2db.persistence.LiteralDataprovider) QueryFunctionType.cast(com.servoy.j2db.query.QueryFunction.QueryFunctionType.cast) Column(com.servoy.j2db.persistence.Column) RelationItem(com.servoy.j2db.persistence.RelationItem) RepositoryException(com.servoy.j2db.persistence.RepositoryException) MEDIA(com.servoy.j2db.persistence.IColumnTypes.MEDIA) Collections(java.util.Collections) TablePlaceholderKey(com.servoy.j2db.query.TablePlaceholderKey) AbstractBaseQuery.deepClone(com.servoy.j2db.query.AbstractBaseQuery.deepClone) QueryFilter(com.servoy.j2db.query.QueryFilter) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) QuerySelect(com.servoy.j2db.query.QuerySelect)

Example 3 with QueryFilter

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

the class SQLGenerator method createTableFiltercondition.

public static QueryFilter createTableFiltercondition(BaseQueryTable qTable, Table table, DataproviderTableFilterdefinition filterdefinition) {
    Column c = table.getColumn(filterdefinition.getDataprovider());
    if (c == null) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        Debug.error("Could not apply filter " + filterdefinition + " on table " + table + " : column not found:" + filterdefinition.getDataprovider());
        return null;
    }
    int op = filterdefinition.getOperator();
    int maskedOp = op & IBaseSQLCondition.OPERATOR_MASK;
    Object value = filterdefinition.getValue();
    QueryColumn qColumn = c.queryColumn(qTable);
    ISQLCondition filterWhere;
    if (maskedOp == IBaseSQLCondition.EQUALS_OPERATOR || maskedOp == IBaseSQLCondition.NOT_OPERATOR || maskedOp == IBaseSQLCondition.IN_OPERATOR || maskedOp == IBaseSQLCondition.NOT_IN_OPERATOR || maskedOp == IBaseSQLCondition.GT_OPERATOR || maskedOp == IBaseSQLCondition.LT_OPERATOR || maskedOp == IBaseSQLCondition.GTE_OPERATOR || maskedOp == IBaseSQLCondition.LTE_OPERATOR) {
        Object inValues;
        boolean andCondition = true;
        if (value instanceof List<?>) {
            inValues = new Object[][] { ((List<?>) value).toArray() };
            andCondition = maskedOp != IBaseSQLCondition.NOT_OPERATOR && maskedOp != IBaseSQLCondition.NOT_IN_OPERATOR;
        } else if (value != null && value.getClass().isArray()) {
            inValues = new Object[][] { (Object[]) value };
            andCondition = maskedOp != IBaseSQLCondition.NOT_OPERATOR && maskedOp != IBaseSQLCondition.NOT_IN_OPERATOR;
        } else {
            if (value != null && isSelectQuery(value.toString())) {
                if ((op & IBaseSQLCondition.IS_SQL_MODIFIER) == 0) {
                    Debug.warn("Filter is created using a custom query without using the sql-modifier, this will be removed in a future version of servoy, please use operator '" + RelationItem.getOperatorAsString(op | IBaseSQLCondition.IS_SQL_MODIFIER) + "'");
                }
                // add as subquery
                inValues = new QueryCustomSelect(value.toString(), null);
            } else {
                if (value != null && (op & IBaseSQLCondition.IS_SQL_MODIFIER) != 0) {
                    Debug.warn("Filter has the sql-modifier, but the value is not valid sql for filters: " + SQL_QUERY_VALIDATION_MESSAGE + ":" + value);
                }
                inValues = new Object[][] { new Object[] { value } };
            }
        }
        // replace values with column type value
        if (inValues instanceof Object[][]) {
            Object[][] array = (Object[][]) inValues;
            for (int i = 0; i < array.length; i++) {
                for (int j = 0; array[i] != null && j < array[i].length; j++) {
                    Object v = c.getAsRightType(array[i][j]);
                    if (v == null)
                        v = ValueFactory.createNullValue(c.getType());
                    array[i][j] = v;
                }
            }
        }
        filterWhere = new SetCondition(op, new IQuerySelectValue[] { qColumn }, inValues, andCondition);
    } else if (maskedOp == IBaseSQLCondition.BETWEEN_OPERATOR || maskedOp == IBaseSQLCondition.NOT_BETWEEN_OPERATOR) {
        Object op1 = null;
        Object op2 = null;
        if (value instanceof List<?> && ((List<?>) value).size() > 1) {
            op1 = ((List<?>) value).get(0);
            op2 = ((List<?>) value).get(1);
        } else if (value != null && value.getClass().isArray() && ((Object[]) value).length > 1) {
            op1 = ((Object[]) value)[0];
            op2 = ((Object[]) value)[1];
        }
        op1 = c.getAsRightType(op1);
        if (op1 == null)
            op1 = ValueFactory.createNullValue(c.getType());
        op2 = c.getAsRightType(op2);
        if (op2 == null)
            op2 = ValueFactory.createNullValue(c.getType());
        filterWhere = new CompareCondition(op, qColumn, new Object[] { op1, op2 });
    } else {
        Object operand;
        if (maskedOp == IBaseSQLCondition.LIKE_OPERATOR || maskedOp == IBaseSQLCondition.NOT_LIKE_OPERATOR) {
            operand = value;
        } else {
            operand = c.getAsRightType(value);
            if (operand == null)
                operand = ValueFactory.createNullValue(c.getType());
        }
        filterWhere = new CompareCondition(op, qColumn, operand);
    }
    return new QueryFilter(filterWhere);
}
Also used : QueryCustomSelect(com.servoy.j2db.query.QueryCustomSelect) SetCondition(com.servoy.j2db.query.SetCondition) ISQLCondition(com.servoy.j2db.query.ISQLCondition) QueryFilter(com.servoy.j2db.query.QueryFilter) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) IColumn(com.servoy.j2db.persistence.IColumn) Column(com.servoy.j2db.persistence.Column) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) CompareCondition(com.servoy.j2db.query.CompareCondition) List(java.util.List) ArrayList(java.util.ArrayList) SafeArrayList(com.servoy.j2db.util.SafeArrayList) Collectors.toList(java.util.stream.Collectors.toList) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue)

Example 4 with QueryFilter

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

the class FoundSet method addFilterParam.

public boolean addFilterParam(String filterName, String dataprovider, String operator, Object value) throws ServoyException {
    DataproviderTableFilterdefinition dataproviderTableFilterdefinition = fsm.createDataproviderTableFilterdefinition(sheet.getTable(), dataprovider, operator, value);
    if (dataproviderTableFilterdefinition == null) {
        fsm.getApplication().reportJSError("Table filter not created, column not found in table or operator invalid, filterName = '" + filterName + "', serverName = '" + sheet.getTable().getServerName() + "', table = '" + sheet.getTable().getName() + "', dataprovider = '" + dataprovider + "', operator = '" + operator + "'", null);
        return false;
    }
    // create condition to check filter
    QueryFilter filtercondition = SQLGenerator.createTableFiltercondition(creationSqlSelect.getTable(), sheet.getTable(), dataproviderTableFilterdefinition);
    if (filtercondition == null) {
        return false;
    }
    return addFilterParam(filterName, dataproviderTableFilterdefinition);
}
Also used : QueryFilter(com.servoy.j2db.query.QueryFilter)

Aggregations

QueryFilter (com.servoy.j2db.query.QueryFilter)4 BaseQueryColumn (com.servoy.base.query.BaseQueryColumn)2 Column (com.servoy.j2db.persistence.Column)2 IColumn (com.servoy.j2db.persistence.IColumn)2 ISQLJoin (com.servoy.j2db.query.ISQLJoin)2 BaseSQLGenerator (com.servoy.base.dataprocessing.BaseSQLGenerator)1 ITypeConverter (com.servoy.base.dataprocessing.ITypeConverter)1 IValueConverter (com.servoy.base.dataprocessing.IValueConverter)1 IDENT_COLUMNS (com.servoy.base.persistence.IBaseColumn.IDENT_COLUMNS)1 UUID_COLUMN (com.servoy.base.persistence.IBaseColumn.UUID_COLUMN)1 BaseColumnType (com.servoy.base.query.BaseColumnType)1 BaseQueryTable (com.servoy.base.query.BaseQueryTable)1 IBaseSQLCondition (com.servoy.base.query.IBaseSQLCondition)1 IQueryConstants (com.servoy.base.query.IQueryConstants)1 FlattenedSolution (com.servoy.j2db.FlattenedSolution)1 IApplication (com.servoy.j2db.IApplication)1 IServiceProvider (com.servoy.j2db.IServiceProvider)1 RelatedFindState (com.servoy.j2db.dataprocessing.FindState.RelatedFindState)1 ConverterInfo (com.servoy.j2db.dataprocessing.SQLSheet.ConverterInfo)1 ASCENDING (com.servoy.j2db.dataprocessing.SortColumn.ASCENDING)1