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

Example 1 with OrCondition

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

the class SQLGenerator method getPKSelectSqlSelect.

/*
 * _____________________________________________________________ The methods below belong to this class
 */
// SQL pk(s) select for foundset,concatenating those strings will always deliver a executable SQL
// Note: removeUnusedJoins must be false when the resulting query is changed afterwards (like adding columns)
QuerySelect getPKSelectSqlSelect(IGlobalValueEntry provider, Table table, QuerySelect oldSQLQuery, List<IRecordInternal> findStates, boolean reduce, IDataSet omitPKs, List<SortColumn> orderByFields, boolean removeUnusedJoins) throws ServoyException {
    if (table == null) {
        throw new RepositoryException(ServoyException.InternalCodes.TABLE_NOT_FOUND);
    }
    QuerySelect retval;
    if (oldSQLQuery != null) {
        retval = deepClone(oldSQLQuery);
        retval.setGroupBy(null);
        // will be generated based on foundset sorting
        if (orderByFields != null)
            retval.clearSorts();
        // remove all servoy conditions, except filter, search and relation
        for (String conditionName : retval.getConditionNames()) {
            if (conditionName.startsWith(SERVOY_CONDITION_PREFIX) && !(CONDITION_FILTER.equals(conditionName) || CONDITION_SEARCH.equals(conditionName) || CONDITION_RELATION.equals(conditionName))) {
                retval.setCondition(conditionName, null);
            }
        }
    } else {
        retval = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    }
    // Example:-select pk1,pk2 from tablename1 where ((fieldname1 like '%abcd%') or ((fieldname2 like '%xyz%')) (retrieve max 200 rows)
    ArrayList<IQuerySelectValue> pkQueryColumns = new ArrayList<IQuerySelectValue>(3);
    ArrayList<Column> pkColumns = new ArrayList<Column>(3);
    // getPrimaryKeys from table
    Iterator<Column> pks = table.getRowIdentColumns().iterator();
    // make select
    if (!pks.hasNext()) {
        throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
    }
    while (pks.hasNext()) {
        Column column = pks.next();
        pkColumns.add(column);
        pkQueryColumns.add(column.queryColumn(retval.getTable()));
    }
    retval.setColumns(pkQueryColumns);
    if (omitPKs != null && omitPKs.getRowCount() != 0) {
        // omit is rebuild each time
        retval.setCondition(CONDITION_OMIT, createSetConditionFromPKs(IBaseSQLCondition.NOT_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), pkColumns, omitPKs));
    } else if (oldSQLQuery != null) {
        retval.setCondition(CONDITION_OMIT, oldSQLQuery.getConditionClone(CONDITION_OMIT));
    }
    if (// new
    findStates != null && findStates.size() != 0) {
        ISQLCondition moreWhere = null;
        for (IRecordInternal obj : findStates) {
            if (obj instanceof FindState) {
                moreWhere = OrCondition.or(moreWhere, createConditionFromFindState((FindState) obj, retval, provider, pkQueryColumns));
            }
        }
        if (moreWhere != null) {
            if (reduce) {
                retval.addCondition(CONDITION_SEARCH, moreWhere);
            } else {
                retval.addConditionOr(CONDITION_SEARCH, moreWhere);
            }
            if (retval.getJoins() != null) {
                // check if the search condition has an or-condition
                final boolean[] hasOr = { false };
                retval.getCondition(CONDITION_SEARCH).acceptVisitor(new IVisitor() {

                    public Object visit(Object o) {
                        if (o instanceof OrCondition && ((OrCondition) o).getConditions().size() > 1) {
                            hasOr[0] = true;
                            return new VisitorResult(o, false);
                        }
                        return o;
                    }
                });
                if (hasOr[0]) {
                    // override join type to left outer join, a related OR-search should not make the result set smaller
                    for (ISQLJoin join : retval.getJoins()) {
                        if (join instanceof QueryJoin && ((QueryJoin) join).getJoinType() == IQueryConstants.INNER_JOIN) {
                            ((QueryJoin) join).setJoinType(IQueryConstants.LEFT_OUTER_JOIN);
                        }
                    }
                }
            }
        }
    }
    // make orderby
    if (orderByFields != null || retval.getSorts() == null) {
        List<SortColumn> orderBy = orderByFields == null ? new ArrayList<SortColumn>(3) : orderByFields;
        if (orderBy.size() == 0) {
            for (Column pkColumn : pkColumns) {
                orderBy.add(new SortColumn(pkColumn));
            }
        }
        addSorts(retval, retval.getTable(), provider, table, orderBy, true, false);
    }
    if (removeUnusedJoins) {
        // remove unneeded joins, some may have been added because of a previous sort and are no longer needed.
        retval.removeUnusedJoins(false);
    }
    // 1 do not remove sort or groupby test, will cause invalid queries
    // 1 this one causes error and can not be fixed,
    // 1 if (joinswherepart.length() != 0 && !sortIsRelated && groupbyKeyword == STRING_EMPTY && table.getPrimaryKeyCount() == 1)
    // 1 sql select distinct(s_contacts.contactsid) from s_contacts,s_companies where s_contacts.company_id = s_companies.company_id order by s_contacts.surname  ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
    // retval may have set distinct and plainPKSelect flag based on previous sort columns, make sure to reset first
    retval.setDistinct(false);
    retval.setPlainPKSelect(false);
    if (// if joined pks comes back multiple times
    retval.getJoins() != null && retval.getColumns().size() == 1 && isDistinctAllowed(retval.getColumns(), retval.getSorts())) {
        retval.setDistinct(true);
    } else if (// plain pk select
    retval.getJoins() == null && retval.getColumns().size() == pkColumns.size()) {
        retval.setPlainPKSelect(true);
    }
    return retval;
}

Example 2 with OrCondition

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

the class LookupListModel method generateWherePart.

/**
 * @param txt
 * @param valueList
 * @param select
 * @param qTable
 */
private boolean generateWherePart(String txt, ValueList valueList, QuerySelect select, BaseQueryTable qTable) {
    if (// $NON-NLS-1$
    txt != null && !txt.equals("")) {
        String[] displayValues = null;
        String separator = valueList.getSeparator();
        if (// $NON-NLS-1$
        separator != null && !separator.equals("")) {
            if (showValues != 1 && showValues != 2 && showValues != 4) {
                // its a combination
                displayValues = Utils.stringSplit(txt, separator);
                if (displayValues.length == 1 && displayValues[0].equals(txt.toUpperCase())) {
                    displayValues = null;
                } else {
                    ArrayList<String> lst = new ArrayList<String>();
                    for (String displayValue : displayValues) {
                        if (// $NON-NLS-1$
                        !displayValue.trim().equals("")) {
                            lst.add(displayValue.toUpperCase() + '%');
                        }
                    }
                    displayValues = lst.toArray(new String[lst.size()]);
                }
            }
        }
        String likeValue = txt.toUpperCase() + '%';
        OrCondition overallOr = new OrCondition();
        if ((showValues & 1) != 0) {
            addOrCondition(valueList.getDataProviderID1(), qTable, likeValue, displayValues, overallOr);
        }
        if ((showValues & 2) != 0) {
            addOrCondition(valueList.getDataProviderID2(), qTable, likeValue, displayValues, overallOr);
        }
        if ((showValues & 4) != 0) {
            addOrCondition(valueList.getDataProviderID3(), qTable, likeValue, displayValues, overallOr);
        }
        select.addCondition(SQLGenerator.CONDITION_SEARCH, overallOr);
        return true;
    }
    return false;
}

Example 3 with OrCondition

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

the class LookupValueList method fill.

private void fill(Object display, Object real) throws ServoyException, RemoteException {
    if (dontQuery || table == null)
        return;
    Object value = null;
    int values = 0;
    if (display != null) {
        values = showValues;
        value = display;
    } else {
        values = returnValues;
        value = real;
    }
    QuerySelect select = null;
    BaseQueryTable qTable = null;
    if (valueList.getDatabaseValuesType() == IValueListConstants.TABLE_VALUES) {
        select = DBValueList.createValuelistQuery(application, valueList, table);
        if (select != null) {
            qTable = select.getTable();
        }
    } else {
        Relation[] relations = application.getFlattenedSolution().getRelationSequence(valueList.getRelationName());
        Pair<QuerySelect, BaseQueryTable> pair = RelatedValueList.createRelatedValuelistQuery(application, valueList, relations, parentState);
        if (pair != null) {
            select = pair.getLeft();
            qTable = pair.getRight();
        }
    }
    if (select == null) {
        return;
    }
    String[] displayValues = null;
    String separator = valueList.getSeparator();
    if (// $NON-NLS-1$
    values == showValues && value != null && separator != null && !separator.equals("")) {
        if (values != 1 && values != 2 && values != 4) {
            // its a combination
            displayValues = Utils.stringSplit(value.toString(), separator);
        }
    }
    OrCondition where = new OrCondition();
    if ((values & 1) != 0) {
        String dp1 = valueList.getDataProviderID1();
        if (displayValues != null) {
            for (String displayValue : displayValues) {
                where.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, DBValueList.getQuerySelectValue(table, qTable, dp1), getAsRightType(dp1, displayValue)));
            }
        }
        // also just add the complete value, for the possibility that it was a value with a separator.
        value = getAsRightType(dp1, value);
        where.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, DBValueList.getQuerySelectValue(table, qTable, dp1), value));
    }
    if ((values & 2) != 0) {
        String dp2 = valueList.getDataProviderID2();
        if (displayValues != null) {
            for (String displayValue : displayValues) {
                where.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, DBValueList.getQuerySelectValue(table, qTable, dp2), getAsRightType(dp2, displayValue)));
            }
        }
        value = getAsRightType(dp2, value);
        where.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, DBValueList.getQuerySelectValue(table, qTable, dp2), value));
    }
    if ((values & 4) != 0) {
        String dp3 = valueList.getDataProviderID3();
        if (displayValues != null) {
            for (String displayValue : displayValues) {
                where.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, DBValueList.getQuerySelectValue(table, qTable, dp3), getAsRightType(dp3, displayValue)));
            }
        }
        value = getAsRightType(dp3, value);
        where.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, DBValueList.getQuerySelectValue(table, qTable, dp3), value));
    }
    select.setCondition(SQLGenerator.CONDITION_SEARCH, where);
    FoundSetManager foundSetManager = ((FoundSetManager) application.getFoundSetManager());
    String transaction_id = foundSetManager.getTransactionID(table.getServerName());
    ArrayList<TableFilter> tableFilterParams = foundSetManager.getTableFilterParams(table.getServerName(), select);
    if (// apply name as filter on column valuelist_name
    valueList.getUseTableFilter()) {
        if (tableFilterParams == null) {
            tableFilterParams = new ArrayList<TableFilter>();
        }
        tableFilterParams.add(new // $NON-NLS-1$
        TableFilter(// $NON-NLS-1$
        "lookupValueList.nameFilter", // $NON-NLS-1$
        table.getServerName(), // $NON-NLS-1$
        table.getName(), // $NON-NLS-1$
        table.getSQLName(), DBValueList.NAME_COLUMN, IBaseSQLCondition.EQUALS_OPERATOR, valueList.getName()));
    }
    SQLStatement trackingInfo = null;
    if (foundSetManager.getEditRecordList().hasAccess(table, IRepository.TRACKING_VIEWS)) {
        trackingInfo = new SQLStatement(ISQLActionTypes.SELECT_ACTION, table.getServerName(), table.getName(), null, null);
        trackingInfo.setTrackingData(select.getColumnNames(), new Object[][] {}, new Object[][] {}, application.getUserUID(), foundSetManager.getTrackingInfo(), application.getClientID());
    }
    IDataSet set = application.getDataServer().performQuery(application.getClientID(), table.getServerName(), transaction_id, select, null, tableFilterParams, !select.isUnique(), 0, maxValuelistRows, IDataServer.VALUELIST_QUERY, trackingInfo);
    String[] displayFormats = getDisplayFormat();
    for (int i = 0; i < set.getRowCount(); i++) {
        Object[] row = CustomValueList.processRow(set.getRow(i), showValues, returnValues);
        DisplayString obj = CustomValueList.handleDisplayData(valueList, displayFormats, concatShowValues, showValues, row, application);
        if (obj != null) {
            alDisplay.add(obj);
            alReal.add(CustomValueList.handleRowData(valueList, concatReturnValues, returnValues, row, application));
        }
    }
}