/** * Gets the arraySubquery for the given arrayColumn in the given contextKey, and maybe adds a JOIN if one is not * already done. * <p> * LEFT JOIN (SELECT id, UNNEST(somecol) AS item, generate_subscripts(somecol, 1) AS pos FROM someschema) _A1 ON * _A1.id = hierarchy.id */ protected ArraySubQuery getArraySubQuery(Table contextHier, String contextKey, Column arrayColumn, boolean skipJoin) { ArraySubQuery arraySubQuery = propertyArraySubQueries.get(contextKey); if (arraySubQuery == null) { String alias = SUBQUERY_ARRAY_ALIAS + ++arraySubQueryJoinCount; arraySubQuery = dialect.getArraySubQuery(arrayColumn, alias); propertyArraySubQueries.put(contextKey, arraySubQuery); if (!skipJoin) { Join join = new Join(Join.LEFT, arraySubQuery.toSql(), alias, null, arraySubQuery.getSubQueryIdColumn().getFullQuotedName(), contextHier.getColumn(Model.MAIN_KEY).getFullQuotedName()); joins.add(join); } } return arraySubQuery; }
contextKey = contextStart + segment + contextSuffix; ArraySubQuery arraySubQuery = getArraySubQuery(contextHier, contextKey, column, skipJoin); column = arraySubQuery.getSubQueryValueColumn(); } else {
protected String getArrayOpSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable, String op) { Table table = arrayColumn.getTable(); String tableAliasName = openQuote() + getTableName(refName) + closeQuote(); String sql = String.format("EXISTS (SELECT 1 FROM %s AS %s WHERE %s = %s AND %s %s ?)", getArraySubQuery(arrayColumn, tableAliasName).toSql(), tableAliasName, dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), tableAliasName + '.' + table.getColumn(Model.MAIN_KEY).getQuotedName(), tableAliasName + '.' + Model.COLL_TABLE_VALUE_KEY, op); if (!positive) { sql = "NOT(" + sql + ")"; } return sql; }