Examples with SqlDocumentFilterConverterContext de.metas.ui.web.document.filter.sql.SqlDocumentFilterConverterContext used on opensource projects

Example 1 with SqlDocumentFilterConverterContext

use of de.metas.ui.web.document.filter.sql.SqlDocumentFilterConverterContext in project metasfresh-webui-api by metasfresh.

the class SqlDocumentQueryBuilder method buildSqlWhereClause.

private IPair<IStringExpression, List<Object>> buildSqlWhereClause() {
    final SqlParamsCollector sqlParams = SqlParamsCollector.newInstance();
    final CompositeStringExpression.Builder sqlWhereClauseBuilder = IStringExpression.composer();
    // 
    // Entity's WHERE clause
    {
        final IStringExpression entityWhereClauseExpression = entityBinding.getSqlWhereClause();
        if (!entityWhereClauseExpression.isNullExpression()) {
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* entity where clause */ (").append(entityWhereClauseExpression).append(")");
        }
    }
    // 
    // Key column
    // FIXME: handle AD_Reference/AD_Ref_List(s). In that case the recordId will be AD_Ref_List.Value,
    // so the SQL where clause which is currently build is AD_Ref_List_ID=<the AD_Ref_List.Value>.
    // The build SQL where clause shall be something like AD_Reference_ID=<the reference, i think we shall fetch it somehow from Lookup> AND Value=<the value, which currently is the recordId>
    final Set<DocumentId> recordIds = getRecordIds();
    if (!recordIds.isEmpty()) {
        final List<SqlDocumentFieldDataBindingDescriptor> keyFields = entityBinding.getKeyFields();
        if (keyFields.isEmpty()) {
            throw new AdempiereException("Failed building where clause because there is no Key Column defined in " + entityBinding);
        }
        // Single primary key
        if (keyFields.size() == 1) {
            final String singleKeyColumnName = keyFields.get(0).getColumnName();
            final ImmutableSet<Integer> recordIdsIntSet = recordIds.stream().map(DocumentId::toInt).collect(ImmutableSet.toImmutableSet());
            sqlWhereClauseBuilder.appendIfNotEmpty("\n /* key */ AND ");
            sqlWhereClauseBuilder.append(DB.buildSqlList(singleKeyColumnName, recordIdsIntSet, sqlParams.toLiveList()));
        } else // Composed primary key
        {
            final boolean parenthesesRequired = !sqlWhereClauseBuilder.isEmpty();
            if (parenthesesRequired) {
                sqlWhereClauseBuilder.append(" AND ( ");
            }
            boolean firstRecord = true;
            final boolean appendParentheses = recordIds.size() > 1;
            for (final DocumentId recordId : recordIds) {
                if (!firstRecord) {
                    sqlWhereClauseBuilder.append("\n OR ");
                }
                if (appendParentheses) {
                    sqlWhereClauseBuilder.append("(");
                }
                final Map<String, Object> keyColumnName2value = extractComposedKey(recordId, keyFields);
                boolean firstKeyPart = true;
                for (final Map.Entry<String, Object> keyPart : keyColumnName2value.entrySet()) {
                    if (!firstKeyPart) {
                        sqlWhereClauseBuilder.append(" AND ");
                    }
                    final String keyColumnName = keyPart.getKey();
                    final Object value = keyPart.getValue();
                    sqlWhereClauseBuilder.append(" ").append(keyColumnName).append("=").append(sqlParams.placeholder(value));
                    firstKeyPart = false;
                }
                if (appendParentheses) {
                    sqlWhereClauseBuilder.append(")");
                }
                firstRecord = false;
            }
            if (parenthesesRequired) {
                sqlWhereClauseBuilder.append(")");
            }
        }
    }
    // 
    // Parent link where clause (if any)
    final Document parentDocument = getParentDocument();
    if (parentDocument != null) {
        final String parentLinkColumnName = entityBinding.getParentLinkColumnName();
        final String linkColumnName = entityBinding.getLinkColumnName();
        if (parentLinkColumnName != null && linkColumnName != null) {
            final IDocumentFieldView parentLinkField = parentDocument.getFieldView(parentLinkColumnName);
            final Object parentLinkValue = parentLinkField.getValue();
            final DocumentFieldWidgetType parentLinkWidgetType = parentLinkField.getWidgetType();
            final Class<?> targetClass = entityBinding.getFieldByFieldName(linkColumnName).getSqlValueClass();
            final Object sqlParentLinkValue = SqlDocumentsRepository.convertValueToPO(parentLinkValue, parentLinkColumnName, parentLinkWidgetType, targetClass);
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* parent link */ ").append(linkColumnName).append("=").append(sqlParams.placeholder(sqlParentLinkValue));
        }
    }
    // 
    // Document filters
    {
        final SqlDocumentFilterConverterContext context = SqlDocumentFilterConverterContext.EMPTY;
        final String sqlFilters = SqlDocumentFilterConverters.createEntityBindingEffectiveConverter(entityBinding).getSql(sqlParams, getDocumentFilters(), SqlOptions.usingTableAlias(entityBinding.getTableAlias()), context);
        if (!Check.isEmpty(sqlFilters, true)) {
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* filters */ (\n").append(sqlFilters).append(")\n");
        }
    }
    // Build the final SQL where clause
    return ImmutablePair.of(sqlWhereClauseBuilder.build(), Collections.unmodifiableList(sqlParams.toList()));
}

Example 2 with SqlDocumentFilterConverterContext

use of de.metas.ui.web.document.filter.sql.SqlDocumentFilterConverterContext in project metasfresh-webui-api by metasfresh.

the class HUIdsFilterHelperTest method testEmptyHUIdsCollection.

/**
 * Verifies that if {@link HUIdsFilterHelper#createFilter(java.util.Collection)} is called with an empty list,
 * then the filter's SQL does <b>not</b> select every single f**king HU on this planet.
 */
@Test
public void testEmptyHUIdsCollection() {
    final DocumentFilter noHusFilter = HUIdsFilterHelper.createFilter(ImmutableList.of());
    final SqlDocumentFilterConverterContext context = SqlDocumentFilterConverterContext.EMPTY;
    final String sql = HUIdsFilterHelper.SQL_DOCUMENT_FILTER_CONVERTER.getSql(SqlParamsCollector.newInstance(), noHusFilter, SqlOptions.usingTableAlias("dummyTableAlias"), context);
    assertThat(sql).doesNotContain(HUIdsSqlDocumentFilterConverter.SQL_TRUE);
}

Example 3 with SqlDocumentFilterConverterContext

use of de.metas.ui.web.document.filter.sql.SqlDocumentFilterConverterContext in project metasfresh-webui-api by metasfresh.

the class FullTextSearchSqlDocumentFilterConverter method getSql.

@Override
public String getSql(final SqlParamsCollector sqlParamsOut, final DocumentFilter filter, final SqlOptions sqlOpts, final SqlDocumentFilterConverterContext context) {
    final String text = filter.getParameterValueAsString(PARAM_SearchText);
    if (Check.isEmpty(text, true)) {
        return "1=1";
    }
    final FullTextSearchFilterContext ftsContext = filter.getParameterValueAs(PARAM_Context);
    // shall not happen
    Check.assumeNotNull(ftsContext, "Parameter ftsContext is not null");
    logger.trace("context: {}", ftsContext);
    final Client elasticsearchClient = ftsContext.getElasticsearchClient();
    final String esIndexName = ftsContext.getEsIndexName();
    final String keyColumnName = ftsContext.getKeyColumnName();
    final String esKeyColumnName = ftsContext.getEsKeyColumnName();
    final QueryBuilder query = QueryBuilders.multiMatchQuery(text, ftsContext.getEsSearchFieldNamesAsArray());
    logger.trace("ES query: {}", query);
    final SearchResponse searchResponse = elasticsearchClient.prepareSearch(esIndexName).setQuery(query).setExplain(logger.isTraceEnabled()).get();
    logger.trace("ES response: {}", searchResponse);
    final List<Integer> recordIds = Stream.of(searchResponse.getHits().getHits()).map(hit -> extractId(hit, esKeyColumnName)).filter(id -> id >= 0).distinct().collect(ImmutableList.toImmutableList());
    logger.trace("Record IDs: {}", recordIds);
    if (recordIds.isEmpty()) {
        return "1=0";
    }
    final String keyColumnNameFQ = sqlOpts.getTableNameOrAlias() + "." + keyColumnName;
    return DB.buildSqlList(keyColumnNameFQ, recordIds, null);
}

Example 4 with SqlDocumentFilterConverterContext

use of de.metas.ui.web.document.filter.sql.SqlDocumentFilterConverterContext in project metasfresh-webui-api by metasfresh.

the class SqlViewSelectionQueryBuilder method buildSqlCreateSelectionFromSelection.

/**
 * @return
 *
 *         <pre>
 * INSERT INTO T_WEBUI_ViewSelection (UUID, Line, keys)
 * SELECT ... FROM T_WEBUI_ViewSelection sel INNER JOIN ourTable WHERE sel.UUID=[fromUUID]
 *         </pre>
 */
public SqlAndParams buildSqlCreateSelectionFromSelection(@NonNull final ViewEvaluationCtx viewEvalCtx, @NonNull final ViewId newViewId, @NonNull final String fromSelectionId, @NonNull final DocumentFilterList filters, @NonNull final DocumentQueryOrderByList orderBys, @NonNull final SqlDocumentFilterConverterContext filterConverterCtx) {
    final String sqlTableAlias = getTableAlias();
    final SqlViewKeyColumnNamesMap keyColumnNamesMap = getSqlViewKeyColumnNamesMap();
    final DocumentQueryOrderByList orderBysEffective = orderBys.stream().flatMap(this::flatMapEffectiveFieldNames).collect(DocumentQueryOrderByList.toDocumentQueryOrderByList());
    // 
    // Build the table we will join.
    final SqlAndParams sqlSourceTable;
    {
        final Set<String> addedFieldNames = new HashSet<>();
        final StringBuilder sqlKeyColumnNames;
        {
            sqlKeyColumnNames = new StringBuilder();
            for (final String keyColumnName : keyColumnNamesMap.getKeyColumnNames()) {
                if (!addedFieldNames.add(keyColumnName)) {
                    continue;
                }
                if (sqlKeyColumnNames.length() > 0) {
                    sqlKeyColumnNames.append("\n, ");
                }
                sqlKeyColumnNames.append(getSqlSelectValue(keyColumnName).withColumnNameAlias(keyColumnName).toSqlStringWithColumnNameAlias());
            }
        }
        final SqlAndParams.Builder sqlSourceTableBuilder = SqlAndParams.builder();
        sqlSourceTableBuilder.append("(SELECT ").append(sqlKeyColumnNames);
        for (final DocumentQueryOrderBy orderBy : orderBysEffective.toList()) {
            final String fieldName = orderBy.getFieldName();
            final SqlSelectDisplayValue sqlSelectDisplayValue = getSqlSelectDisplayValue(fieldName);
            if (sqlSelectDisplayValue != null && addedFieldNames.add(sqlSelectDisplayValue.getColumnNameAlias())) {
                sqlSourceTableBuilder.append("\n, ").append(sqlSelectDisplayValue.withJoinOnTableNameOrAlias(getTableName()).toSqlStringWithColumnNameAlias(viewEvalCtx.toEvaluatee()));
            }
            final SqlSelectValue sqlSelectValue = getSqlSelectValue(fieldName);
            if (sqlSelectValue != null && addedFieldNames.add(sqlSelectValue.getColumnNameAlias())) {
                sqlSourceTableBuilder.append("\n, ").append(sqlSelectValue.withJoinOnTableNameOrAlias(getTableName()).toSqlStringWithColumnNameAlias());
            }
        }
        sqlSourceTableBuilder.append("\n FROM ").append(getTableName());
        final SqlAndParams sqlFilters = buildSqlFiltersOrNull(filters, filterConverterCtx, SqlOptions.usingTableName(getTableName()));
        if (sqlFilters != null) {
            sqlSourceTableBuilder.append("\n WHERE ").append(sqlFilters);
        }
        sqlSourceTableBuilder.append(")");
        sqlSourceTable = sqlSourceTableBuilder.build();
    }
    // 
    // Order BY
    final String sqlOrderBys = SqlDocumentOrderByBuilder.newInstance(this::getFieldOrderBy).joinOnTableNameOrAlias(sqlTableAlias).useColumnNameAlias(true).buildSqlOrderBy(orderBysEffective).map(sqlOrderBysExpr -> sqlOrderBysExpr.evaluate(viewEvalCtx.toEvaluatee(), OnVariableNotFound.Fail)).map(sql -> _viewBinding.replaceTableNameWithTableAlias(sql, sqlTableAlias)).orElse(null);
    // 
    final String sqlJoinCondition = keyColumnNamesMap.getSqlJoinCondition(sqlTableAlias, "sel");
    // 
    return SqlAndParams.builder().append("INSERT INTO " + I_T_WEBUI_ViewSelection.Table_Name + " (" + " " + I_T_WEBUI_ViewSelection.COLUMNNAME_UUID + ", " + I_T_WEBUI_ViewSelection.COLUMNNAME_Line + ", " + keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated() + ")").append("\n SELECT ").append("\n  ?", // newUUID
    newViewId.getViewId()).append("\n, ").append("row_number() OVER (").append(sqlOrderBys != null ? "ORDER BY " + sqlOrderBys : "").append(// Line
    ")").append("\n, ").append(// keys
    keyColumnNamesMap.getKeyColumnNamesCommaSeparated()).append("\n FROM ").append(I_T_WEBUI_ViewSelection.Table_Name).append(" sel").append("\n INNER JOIN ").append(sqlSourceTable).append(" ").append(sqlTableAlias).append(" ON (").append(sqlJoinCondition).append(")").append("\n WHERE sel.").append(I_T_WEBUI_ViewSelection.COLUMNNAME_UUID).append("=?", // fromUUID
    fromSelectionId).build();
}