@Test void getSqlWhere() { Package eric = createPackage("eu_bbmri_eric"); Attribute collectionsIdAttribute = createIdAttribute("collectionsId"); EntityType collectionsEntity = createMockEntityWithIdAttribute( "eu_bbmri_eric_collections", collectionsIdAttribute, "collectionsId"); Attribute typeIdAttribute = createIdAttribute("typeId"); EntityType typeEntity = createMockEntityWithIdAttribute("eu_bbmri_eric_type", typeIdAttribute, "typeId"); Attribute categoryIdAttribute = createIdAttribute("categoryId"); EntityType categoryEntity = createMockEntityWithIdAttribute( "eu_bbmri_eric_category", categoryIdAttribute, "categoryId"); Attribute typeAttribute = createMrefAttribute("type", typeEntity); Attribute categoryAttribute = createMrefAttribute("data_categories", categoryEntity); when(collectionsEntity.getPackage()).thenReturn(eric); when(collectionsEntity.getAttribute("type")).thenReturn(typeAttribute); when(collectionsEntity.getAttribute("data_categories")).thenReturn(categoryAttribute); when(collectionsEntity.getAtomicAttributes()) .thenReturn(asList(collectionsIdAttribute, typeAttribute, categoryAttribute)); QueryRule typeRule = new QueryRule( NESTED, newArrayList( new QueryRule("type", EQUALS, "POPULATION_BASED"), new QueryRule(OR), new QueryRule("type", EQUALS, "QUALITY_CONTROL"))); QueryRule categoryRule = new QueryRule( NESTED, newArrayList( new QueryRule("data_categories", EQUALS, "MEDICAL_RECORDS"), new QueryRule(OR), new QueryRule("data_categories", EQUALS, "NATIONAL_REGISTRIES"))); QueryImpl<Entity> q = new QueryImpl<>( new QueryRule( NESTED, newArrayList(newArrayList(typeRule, new QueryRule(AND), categoryRule)))); List<Object> parameters = Lists.newArrayList(); String sqlWhere = PostgreSqlQueryGenerator.getSqlWhere(collectionsEntity, q, parameters, new AtomicInteger()); assertEquals( "((\"type_filter1\".\"type\" = ? OR \"type_filter2\".\"type\" = ?)" + " AND " + "(\"data_categories_filter3\".\"data_categories\" = ? OR \"data_categories_filter4\".\"data_categories\" = ?))", sqlWhere); }
static <E extends Entity> String getSqlWhere( EntityType entityType, Query<E> q, List<Object> parameters, AtomicInteger mrefFilterIndex) { StringBuilder result = new StringBuilder(); for (QueryRule r : q.getRules()) { Attribute attr = null; if (r.getField() != null) { attr = entityType.getAttribute(r.getField()); if (attr == null) { throw new MolgenisDataException(format("Unknown attribute [%s]", r.getField())); } if (isPersistedInOtherTable(attr)) { mrefFilterIndex.incrementAndGet(); } } StringBuilder predicate = new StringBuilder(); Operator operator = r.getOperator(); switch (operator) { case AND: result.append(" AND "); break; case NESTED: QueryImpl<Entity> nestedQ = new QueryImpl<>(r.getNestedRules()); result .append('(') .append(getSqlWhere(entityType, nestedQ, parameters, mrefFilterIndex)) .append(')'); break; case OR: result.append(" OR "); break; case LIKE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, LIKE)); } String columnName; if (isPersistedInOtherTable(attr)) { columnName = getFilterColumnName(attr, mrefFilterIndex.get()); } else { columnName = "this." + getColumnName(attr); } if (isStringType(attr) || isTextType(attr)) { result.append(' ').append(columnName); } else { result.append(" CAST(").append(columnName).append(" as TEXT)"); } result.append(isTaggedType(attr, Vocabulary.CASE_SENSITIVE) ? " LIKE ?" : " ILIKE ?"); parameters.add("%" + PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr) + '%'); break; case IN: getSqlWhereForInQueryRule(r, entityType, attr, parameters, mrefFilterIndex, result); break; case NOT: result.append(" NOT "); break; case RANGE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, RANGE)); } Object range = r.getValue(); if (range == null) { throw new MolgenisDataException("Missing value for RANGE query"); } if (!(range instanceof Iterable<?>)) { throw new MolgenisDataException( format( "RANGE value is of type [%s] instead of [Iterable]", range.getClass().getSimpleName())); } Iterator<?> rangeValues = ((Iterable<?>) range).iterator(); parameters.add(rangeValues.next()); parameters.add(rangeValues.next()); StringBuilder column = new StringBuilder(); if (isPersistedInOtherTable(attr)) { column.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { column.append("this"); } column.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); predicate.append(column).append(" >= ? AND ").append(column).append(" <= ?"); result.append(predicate); break; case EQUALS: if (attr == null) { throw new MolgenisDataException("Missing attribute field in EQUALS query rule"); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } Attribute equalsAttr; if (attr.isMappedBy()) { equalsAttr = attr.getRefEntity().getIdAttribute(); } else { equalsAttr = entityType.getAttribute(r.getField()); } predicate.append('.').append(getColumnName(equalsAttr)); if (r.getValue() == null) { predicate.append(" IS NULL "); } else { Object postgreSqlVal = PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr); if (attr.getDataType() == BOOL) { Boolean bool = (Boolean) postgreSqlVal; if (bool == null) { throw new NullPointerException(); } if (Boolean.TRUE.equals(bool)) { predicate.append(" IS TRUE"); } else { predicate.append(" IS FALSE"); } } else { predicate.append(" ="); predicate.append(" ? "); parameters.add(postgreSqlVal); } } if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case GREATER: case GREATER_EQUAL: case LESS: case LESS_EQUAL: if (attr == null) { throw new NullPointerException( format( UNSPECIFIED_ATTRIBUTE_MSG, format("%s, %s, %s or %s", GREATER, GREATER_EQUAL, LESS, LESS_EQUAL))); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } predicate.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); switch (operator) { case GREATER: predicate.append(" >"); break; case GREATER_EQUAL: predicate.append(" >="); break; case LESS: predicate.append(" <"); break; case LESS_EQUAL: predicate.append(" <="); break; default: throw new RuntimeException(format("Unexpected query operator [%s]", operator)); } predicate.append(" ? "); parameters.add(PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr)); if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case DIS_MAX: case FUZZY_MATCH: case FUZZY_MATCH_NGRAM: case SEARCH: case SEARCH_QUERY: case SHOULD: throw new UnsupportedOperationException( format( "Query operator [%s] not supported by PostgreSQL repository", operator.toString())); default: throw new UnexpectedEnumException(operator); } } return result.toString().trim(); }
PostgreSqlQueryGenerator { static <E extends Entity> String getSqlWhere( EntityType entityType, Query<E> q, List<Object> parameters, AtomicInteger mrefFilterIndex) { StringBuilder result = new StringBuilder(); for (QueryRule r : q.getRules()) { Attribute attr = null; if (r.getField() != null) { attr = entityType.getAttribute(r.getField()); if (attr == null) { throw new MolgenisDataException(format("Unknown attribute [%s]", r.getField())); } if (isPersistedInOtherTable(attr)) { mrefFilterIndex.incrementAndGet(); } } StringBuilder predicate = new StringBuilder(); Operator operator = r.getOperator(); switch (operator) { case AND: result.append(" AND "); break; case NESTED: QueryImpl<Entity> nestedQ = new QueryImpl<>(r.getNestedRules()); result .append('(') .append(getSqlWhere(entityType, nestedQ, parameters, mrefFilterIndex)) .append(')'); break; case OR: result.append(" OR "); break; case LIKE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, LIKE)); } String columnName; if (isPersistedInOtherTable(attr)) { columnName = getFilterColumnName(attr, mrefFilterIndex.get()); } else { columnName = "this." + getColumnName(attr); } if (isStringType(attr) || isTextType(attr)) { result.append(' ').append(columnName); } else { result.append(" CAST(").append(columnName).append(" as TEXT)"); } result.append(isTaggedType(attr, Vocabulary.CASE_SENSITIVE) ? " LIKE ?" : " ILIKE ?"); parameters.add("%" + PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr) + '%'); break; case IN: getSqlWhereForInQueryRule(r, entityType, attr, parameters, mrefFilterIndex, result); break; case NOT: result.append(" NOT "); break; case RANGE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, RANGE)); } Object range = r.getValue(); if (range == null) { throw new MolgenisDataException("Missing value for RANGE query"); } if (!(range instanceof Iterable<?>)) { throw new MolgenisDataException( format( "RANGE value is of type [%s] instead of [Iterable]", range.getClass().getSimpleName())); } Iterator<?> rangeValues = ((Iterable<?>) range).iterator(); parameters.add(rangeValues.next()); parameters.add(rangeValues.next()); StringBuilder column = new StringBuilder(); if (isPersistedInOtherTable(attr)) { column.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { column.append("this"); } column.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); predicate.append(column).append(" >= ? AND ").append(column).append(" <= ?"); result.append(predicate); break; case EQUALS: if (attr == null) { throw new MolgenisDataException("Missing attribute field in EQUALS query rule"); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } Attribute equalsAttr; if (attr.isMappedBy()) { equalsAttr = attr.getRefEntity().getIdAttribute(); } else { equalsAttr = entityType.getAttribute(r.getField()); } predicate.append('.').append(getColumnName(equalsAttr)); if (r.getValue() == null) { predicate.append(" IS NULL "); } else { Object postgreSqlVal = PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr); if (attr.getDataType() == BOOL) { Boolean bool = (Boolean) postgreSqlVal; if (bool == null) { throw new NullPointerException(); } if (Boolean.TRUE.equals(bool)) { predicate.append(" IS TRUE"); } else { predicate.append(" IS FALSE"); } } else { predicate.append(" ="); predicate.append(" ? "); parameters.add(postgreSqlVal); } } if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case GREATER: case GREATER_EQUAL: case LESS: case LESS_EQUAL: if (attr == null) { throw new NullPointerException( format( UNSPECIFIED_ATTRIBUTE_MSG, format("%s, %s, %s or %s", GREATER, GREATER_EQUAL, LESS, LESS_EQUAL))); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } predicate.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); switch (operator) { case GREATER: predicate.append(" >"); break; case GREATER_EQUAL: predicate.append(" >="); break; case LESS: predicate.append(" <"); break; case LESS_EQUAL: predicate.append(" <="); break; default: throw new RuntimeException(format("Unexpected query operator [%s]", operator)); } predicate.append(" ? "); parameters.add(PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr)); if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case DIS_MAX: case FUZZY_MATCH: case FUZZY_MATCH_NGRAM: case SEARCH: case SEARCH_QUERY: case SHOULD: throw new UnsupportedOperationException( format( "Query operator [%s] not supported by PostgreSQL repository", operator.toString())); default: throw new UnexpectedEnumException(operator); } } return result.toString().trim(); } }
PostgreSqlQueryGenerator { static <E extends Entity> String getSqlWhere( EntityType entityType, Query<E> q, List<Object> parameters, AtomicInteger mrefFilterIndex) { StringBuilder result = new StringBuilder(); for (QueryRule r : q.getRules()) { Attribute attr = null; if (r.getField() != null) { attr = entityType.getAttribute(r.getField()); if (attr == null) { throw new MolgenisDataException(format("Unknown attribute [%s]", r.getField())); } if (isPersistedInOtherTable(attr)) { mrefFilterIndex.incrementAndGet(); } } StringBuilder predicate = new StringBuilder(); Operator operator = r.getOperator(); switch (operator) { case AND: result.append(" AND "); break; case NESTED: QueryImpl<Entity> nestedQ = new QueryImpl<>(r.getNestedRules()); result .append('(') .append(getSqlWhere(entityType, nestedQ, parameters, mrefFilterIndex)) .append(')'); break; case OR: result.append(" OR "); break; case LIKE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, LIKE)); } String columnName; if (isPersistedInOtherTable(attr)) { columnName = getFilterColumnName(attr, mrefFilterIndex.get()); } else { columnName = "this." + getColumnName(attr); } if (isStringType(attr) || isTextType(attr)) { result.append(' ').append(columnName); } else { result.append(" CAST(").append(columnName).append(" as TEXT)"); } result.append(isTaggedType(attr, Vocabulary.CASE_SENSITIVE) ? " LIKE ?" : " ILIKE ?"); parameters.add("%" + PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr) + '%'); break; case IN: getSqlWhereForInQueryRule(r, entityType, attr, parameters, mrefFilterIndex, result); break; case NOT: result.append(" NOT "); break; case RANGE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, RANGE)); } Object range = r.getValue(); if (range == null) { throw new MolgenisDataException("Missing value for RANGE query"); } if (!(range instanceof Iterable<?>)) { throw new MolgenisDataException( format( "RANGE value is of type [%s] instead of [Iterable]", range.getClass().getSimpleName())); } Iterator<?> rangeValues = ((Iterable<?>) range).iterator(); parameters.add(rangeValues.next()); parameters.add(rangeValues.next()); StringBuilder column = new StringBuilder(); if (isPersistedInOtherTable(attr)) { column.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { column.append("this"); } column.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); predicate.append(column).append(" >= ? AND ").append(column).append(" <= ?"); result.append(predicate); break; case EQUALS: if (attr == null) { throw new MolgenisDataException("Missing attribute field in EQUALS query rule"); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } Attribute equalsAttr; if (attr.isMappedBy()) { equalsAttr = attr.getRefEntity().getIdAttribute(); } else { equalsAttr = entityType.getAttribute(r.getField()); } predicate.append('.').append(getColumnName(equalsAttr)); if (r.getValue() == null) { predicate.append(" IS NULL "); } else { Object postgreSqlVal = PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr); if (attr.getDataType() == BOOL) { Boolean bool = (Boolean) postgreSqlVal; if (bool == null) { throw new NullPointerException(); } if (Boolean.TRUE.equals(bool)) { predicate.append(" IS TRUE"); } else { predicate.append(" IS FALSE"); } } else { predicate.append(" ="); predicate.append(" ? "); parameters.add(postgreSqlVal); } } if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case GREATER: case GREATER_EQUAL: case LESS: case LESS_EQUAL: if (attr == null) { throw new NullPointerException( format( UNSPECIFIED_ATTRIBUTE_MSG, format("%s, %s, %s or %s", GREATER, GREATER_EQUAL, LESS, LESS_EQUAL))); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } predicate.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); switch (operator) { case GREATER: predicate.append(" >"); break; case GREATER_EQUAL: predicate.append(" >="); break; case LESS: predicate.append(" <"); break; case LESS_EQUAL: predicate.append(" <="); break; default: throw new RuntimeException(format("Unexpected query operator [%s]", operator)); } predicate.append(" ? "); parameters.add(PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr)); if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case DIS_MAX: case FUZZY_MATCH: case FUZZY_MATCH_NGRAM: case SEARCH: case SEARCH_QUERY: case SHOULD: throw new UnsupportedOperationException( format( "Query operator [%s] not supported by PostgreSQL repository", operator.toString())); default: throw new UnexpectedEnumException(operator); } } return result.toString().trim(); } private  PostgreSqlQueryGenerator(); }
PostgreSqlQueryGenerator { static <E extends Entity> String getSqlWhere( EntityType entityType, Query<E> q, List<Object> parameters, AtomicInteger mrefFilterIndex) { StringBuilder result = new StringBuilder(); for (QueryRule r : q.getRules()) { Attribute attr = null; if (r.getField() != null) { attr = entityType.getAttribute(r.getField()); if (attr == null) { throw new MolgenisDataException(format("Unknown attribute [%s]", r.getField())); } if (isPersistedInOtherTable(attr)) { mrefFilterIndex.incrementAndGet(); } } StringBuilder predicate = new StringBuilder(); Operator operator = r.getOperator(); switch (operator) { case AND: result.append(" AND "); break; case NESTED: QueryImpl<Entity> nestedQ = new QueryImpl<>(r.getNestedRules()); result .append('(') .append(getSqlWhere(entityType, nestedQ, parameters, mrefFilterIndex)) .append(')'); break; case OR: result.append(" OR "); break; case LIKE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, LIKE)); } String columnName; if (isPersistedInOtherTable(attr)) { columnName = getFilterColumnName(attr, mrefFilterIndex.get()); } else { columnName = "this." + getColumnName(attr); } if (isStringType(attr) || isTextType(attr)) { result.append(' ').append(columnName); } else { result.append(" CAST(").append(columnName).append(" as TEXT)"); } result.append(isTaggedType(attr, Vocabulary.CASE_SENSITIVE) ? " LIKE ?" : " ILIKE ?"); parameters.add("%" + PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr) + '%'); break; case IN: getSqlWhereForInQueryRule(r, entityType, attr, parameters, mrefFilterIndex, result); break; case NOT: result.append(" NOT "); break; case RANGE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, RANGE)); } Object range = r.getValue(); if (range == null) { throw new MolgenisDataException("Missing value for RANGE query"); } if (!(range instanceof Iterable<?>)) { throw new MolgenisDataException( format( "RANGE value is of type [%s] instead of [Iterable]", range.getClass().getSimpleName())); } Iterator<?> rangeValues = ((Iterable<?>) range).iterator(); parameters.add(rangeValues.next()); parameters.add(rangeValues.next()); StringBuilder column = new StringBuilder(); if (isPersistedInOtherTable(attr)) { column.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { column.append("this"); } column.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); predicate.append(column).append(" >= ? AND ").append(column).append(" <= ?"); result.append(predicate); break; case EQUALS: if (attr == null) { throw new MolgenisDataException("Missing attribute field in EQUALS query rule"); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } Attribute equalsAttr; if (attr.isMappedBy()) { equalsAttr = attr.getRefEntity().getIdAttribute(); } else { equalsAttr = entityType.getAttribute(r.getField()); } predicate.append('.').append(getColumnName(equalsAttr)); if (r.getValue() == null) { predicate.append(" IS NULL "); } else { Object postgreSqlVal = PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr); if (attr.getDataType() == BOOL) { Boolean bool = (Boolean) postgreSqlVal; if (bool == null) { throw new NullPointerException(); } if (Boolean.TRUE.equals(bool)) { predicate.append(" IS TRUE"); } else { predicate.append(" IS FALSE"); } } else { predicate.append(" ="); predicate.append(" ? "); parameters.add(postgreSqlVal); } } if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case GREATER: case GREATER_EQUAL: case LESS: case LESS_EQUAL: if (attr == null) { throw new NullPointerException( format( UNSPECIFIED_ATTRIBUTE_MSG, format("%s, %s, %s or %s", GREATER, GREATER_EQUAL, LESS, LESS_EQUAL))); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } predicate.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); switch (operator) { case GREATER: predicate.append(" >"); break; case GREATER_EQUAL: predicate.append(" >="); break; case LESS: predicate.append(" <"); break; case LESS_EQUAL: predicate.append(" <="); break; default: throw new RuntimeException(format("Unexpected query operator [%s]", operator)); } predicate.append(" ? "); parameters.add(PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr)); if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case DIS_MAX: case FUZZY_MATCH: case FUZZY_MATCH_NGRAM: case SEARCH: case SEARCH_QUERY: case SHOULD: throw new UnsupportedOperationException( format( "Query operator [%s] not supported by PostgreSQL repository", operator.toString())); default: throw new UnexpectedEnumException(operator); } } return result.toString().trim(); } private  PostgreSqlQueryGenerator();  }
PostgreSqlQueryGenerator { static <E extends Entity> String getSqlWhere( EntityType entityType, Query<E> q, List<Object> parameters, AtomicInteger mrefFilterIndex) { StringBuilder result = new StringBuilder(); for (QueryRule r : q.getRules()) { Attribute attr = null; if (r.getField() != null) { attr = entityType.getAttribute(r.getField()); if (attr == null) { throw new MolgenisDataException(format("Unknown attribute [%s]", r.getField())); } if (isPersistedInOtherTable(attr)) { mrefFilterIndex.incrementAndGet(); } } StringBuilder predicate = new StringBuilder(); Operator operator = r.getOperator(); switch (operator) { case AND: result.append(" AND "); break; case NESTED: QueryImpl<Entity> nestedQ = new QueryImpl<>(r.getNestedRules()); result .append('(') .append(getSqlWhere(entityType, nestedQ, parameters, mrefFilterIndex)) .append(')'); break; case OR: result.append(" OR "); break; case LIKE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, LIKE)); } String columnName; if (isPersistedInOtherTable(attr)) { columnName = getFilterColumnName(attr, mrefFilterIndex.get()); } else { columnName = "this." + getColumnName(attr); } if (isStringType(attr) || isTextType(attr)) { result.append(' ').append(columnName); } else { result.append(" CAST(").append(columnName).append(" as TEXT)"); } result.append(isTaggedType(attr, Vocabulary.CASE_SENSITIVE) ? " LIKE ?" : " ILIKE ?"); parameters.add("%" + PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr) + '%'); break; case IN: getSqlWhereForInQueryRule(r, entityType, attr, parameters, mrefFilterIndex, result); break; case NOT: result.append(" NOT "); break; case RANGE: if (attr == null) { throw new NullPointerException(format(UNSPECIFIED_ATTRIBUTE_MSG, RANGE)); } Object range = r.getValue(); if (range == null) { throw new MolgenisDataException("Missing value for RANGE query"); } if (!(range instanceof Iterable<?>)) { throw new MolgenisDataException( format( "RANGE value is of type [%s] instead of [Iterable]", range.getClass().getSimpleName())); } Iterator<?> rangeValues = ((Iterable<?>) range).iterator(); parameters.add(rangeValues.next()); parameters.add(rangeValues.next()); StringBuilder column = new StringBuilder(); if (isPersistedInOtherTable(attr)) { column.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { column.append("this"); } column.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); predicate.append(column).append(" >= ? AND ").append(column).append(" <= ?"); result.append(predicate); break; case EQUALS: if (attr == null) { throw new MolgenisDataException("Missing attribute field in EQUALS query rule"); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } Attribute equalsAttr; if (attr.isMappedBy()) { equalsAttr = attr.getRefEntity().getIdAttribute(); } else { equalsAttr = entityType.getAttribute(r.getField()); } predicate.append('.').append(getColumnName(equalsAttr)); if (r.getValue() == null) { predicate.append(" IS NULL "); } else { Object postgreSqlVal = PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr); if (attr.getDataType() == BOOL) { Boolean bool = (Boolean) postgreSqlVal; if (bool == null) { throw new NullPointerException(); } if (Boolean.TRUE.equals(bool)) { predicate.append(" IS TRUE"); } else { predicate.append(" IS FALSE"); } } else { predicate.append(" ="); predicate.append(" ? "); parameters.add(postgreSqlVal); } } if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case GREATER: case GREATER_EQUAL: case LESS: case LESS_EQUAL: if (attr == null) { throw new NullPointerException( format( UNSPECIFIED_ATTRIBUTE_MSG, format("%s, %s, %s or %s", GREATER, GREATER_EQUAL, LESS, LESS_EQUAL))); } if (isPersistedInOtherTable(attr)) { predicate.append(getFilterColumnName(attr, mrefFilterIndex.get())); } else { predicate.append("this"); } predicate.append('.').append(getColumnName(entityType.getAttribute(r.getField()))); switch (operator) { case GREATER: predicate.append(" >"); break; case GREATER_EQUAL: predicate.append(" >="); break; case LESS: predicate.append(" <"); break; case LESS_EQUAL: predicate.append(" <="); break; default: throw new RuntimeException(format("Unexpected query operator [%s]", operator)); } predicate.append(" ? "); parameters.add(PostgreSqlUtils.getPostgreSqlQueryValue(r.getValue(), attr)); if (result.length() > 0 && !result.toString().endsWith(" OR ") && !result.toString().endsWith(" AND ") && !result.toString().endsWith(" NOT ")) { result.append(" AND "); } result.append(predicate); break; case DIS_MAX: case FUZZY_MATCH: case FUZZY_MATCH_NGRAM: case SEARCH: case SEARCH_QUERY: case SHOULD: throw new UnsupportedOperationException( format( "Query operator [%s] not supported by PostgreSQL repository", operator.toString())); default: throw new UnexpectedEnumException(operator); } } return result.toString().trim(); } private  PostgreSqlQueryGenerator();   }