CODEWITHSUNDEEP
javaspring-bootspring-dataspring-data-restquerydsl
aPokeIntheEye
aPokeIntheEye

Reputation: 161

Multi-column "OR" in Spring Data Rest with QueryDsl

Is it possible with Spring (Boot) Data Rest and QueryDsl to perform an "or" search spanning multiple columns? I have already customized my bindings so that an "or" search will be executed when the same search path is present multiple times (MultiValueBinding). myentity?name=foo&name=bar will return all entities where the name attribute either contains fooor bar.

@Entity
MyEntity {
    @Id
    Long id;
    String name;
    String email;
}

@RepositoryRestResource(path = "myentity")
public interface MyEntityRepository extends CrudRepository<MyEntity, Long>, ,
        QuerydslPredicateExecutor<MyEntity>, QuerydslBinderCustomizer<QMyEntity> {
    @Override
    default void customize(QuerydslBindings bindings, QMyEntity root) {
        bindings.bind(root.id).first(NumberExpression::eq);
        bindings.bind(String.class).all((StringPath path, Collection<? extends String> values) -> {
            BooleanBuilder predicate = new BooleanBuilder();
            values.forEach(value -> predicate.or(path.containsIgnoreCase(value)));
            return Optional.of(predicate);
        });
    }
}

Without implementing a custom controller, is it possible to connect searches in multiple paths via "or" so that myentity?name=foo&email=bar will return results where the name attribute contains "foo" or the the email attribute contains "bar". Alternatively defining a custom path which is then used for searches in multiple columns? e.g. myentity?nameOrEmail=foo.

Upvotes: 1

Views: 1969

Answers (2)

torshid
torshid

Reputation: 167

You may just use this library: https://github.com/turkraft/spring-filter

It will let you run search queries such as:

/search?filter= average(ratings) > 4.5 and brand.name in ('audi', 'land rover') and (year > 2018 or km < 50000) and color : 'white' and accidents is empty

You may also check https://github.com/jirutka/rsql-parser but it is a bit outdated

Upvotes: 2

vp131
vp131

Reputation: 97

Try using this implementation:

@Override
default void customize(QuerydslBindings bindings, QDocument root) {

StringPath[] multiPropertySearchPaths = new StringPath[] {root.prop1, root.prop2, root.prop3};

/**
 * Binds prop1, prop2 and prop3 in OR clause
 * This binding will activate when one of the given properties are searched in query params
 */
bindings.bind(multiPropertySearchPaths).all(new MultiValueBinding<StringPath, String>() {
    @Override
    public Predicate bind(StringPath path, Collection<? extends String> values) {
        BooleanBuilder predicate = new BooleanBuilder();
        // Bind paths present in array multiPropertySearchPaths with incoming values
        for (StringPath propertyPath : multiPropertySearchPaths) {
            values.forEach(value -> predicate.or(propertyPath.containsIgnoreCase(value)));
        }
        return predicate;
    }
});
}

It will generate sql like:

select * from document
where
    lower(document.prop1) like ?1 
    or lower(document.prop2) like ?1 
    or lower(document.prop3) like ?1

Upvotes: 0

Related Questions