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