Nested jpa object search

[FloresStefano]

Hi, great work!
I have only a problem with nested object.
I mix your rsql-parser with specification like explaned by Eugen Paraschiv.
I have 2 entity PERSON and ADDRESS (1,n) .
I want to filter the person who have the address id = 5

When I perform the search action dont work!
http://localhost:9090/api/persons/search?q=addresses.id==5
Error:
Unable to locate Attribute with the the given name [addresses.id]

In my jpa entity Person.addresses exist!
This type of search is supported?
What I wrong?

[pauloricardonoe]

Did you solve the issue?

[FloresStefano]

No. I implemented some similar query language by myself. Sorry

[pauloricardonoe]

Ok. Thanks.

[manosbatsis]

Did this some time ago in a pet project sandbox, might help if you are using spring:
https://github.com/manosbatsis/restdude/tree/master/restdude-rsql/src/main/java/com/restdude/rsql

[zifnab87]

This might be interesting to some: https://github.com/zifnab87/spring-boot-rest-api-helpers

[perfectstrong]

This may be late but i hope someone will find this useful later. We need to make parse the compound property (e.g a.b.c).

This piece of code bases on the skeleton here https://www.baeldung.com/rest-api-search-language-rsql-fiql with some minor correction from the suggestion of @manosbatsis.

@AllArgsConstructor
public class GenericRsqlSpecification<T> implements Specification<T> {

    private String property;
    private ComparisonOperator operator;
    private List<String> arguments;

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
                                 CriteriaBuilder builder) {
        Path<String> propertyExpression = parseProperty(root);
        List<Object> args = castArguments(propertyExpression);
        Object argument = args.get(0);
        switch (RsqlSearchOperation.getSimpleOrDefaultEqualOperator(operator)) {
            case EQUAL:
                if (argument instanceof String)
                    return builder.like(propertyExpression,
                            argument.toString().replace('*', '%'));
                else if (argument == null)
                    return builder.isNull(propertyExpression);
                else return builder.equal(propertyExpression, argument);

            case NOT_EQUAL:
                if (argument instanceof String)
                    return builder.notLike(propertyExpression,
                            argument.toString().replace('*', '%'));
                else if (argument == null)
                    return builder.isNotNull(propertyExpression);
                else return builder.notEqual(propertyExpression, argument);

            case GREATER_THAN:
                return builder.greaterThan(propertyExpression,
                        argument.toString());

            case GREATER_THAN_OR_EQUAL:
                return builder.greaterThanOrEqualTo(propertyExpression,
                        argument.toString());

            case LESS_THAN:
                return builder.lessThan(propertyExpression,
                        argument.toString());

            case LESS_THAN_OR_EQUAL:
                return builder.lessThanOrEqualTo(propertyExpression,
                        argument.toString());
            case IN:
                return propertyExpression.in(args);
            case NOT_IN:
                return builder.not(propertyExpression.in(args));
        }

        return null;
    }

    // This method will help us diving deep into nested property using the dot convention
    // The originial tutorial did not have this, so it can only parse the shallow properties.
    private Path<String> parseProperty(Root<T> root) {
        Path<String> path;
        if (property.contains(".")) {
            // Nested properties
            String[] pathSteps = property.split("\\.");
            String step = pathSteps[0];
            path = root.get(step);

            for (int i = 1; i <= pathSteps.length - 1; i++) {
                path = path.get(pathSteps[i]);
            }
        } else {
            path = root.get(property);
        }
        return path;
    }

    private List<Object> castArguments(Path<?> propertyExpression) {
        Class<?> type = propertyExpression.getJavaType();

        return arguments.stream().map(arg -> {
            if (type.equals(Integer.class)) return Integer.parseInt(arg);
            else if (type.equals(Long.class)) return Long.parseLong(arg);
            else if (type.equals(Byte.class)) return Byte.parseByte(arg);
            else return arg;
        }).collect(Collectors.toList());
    }
}

[austalakov]

Thanks @perfectstrong, I made a small addition to make it the parseProperty method work with collections and nested objects as well, in case it helps anyone:

    private Path<String> parseProperty(Root<T> root) {
        Path<String> path;
        if (property.contains(".")) {
            // Nested properties
            String[] pathSteps = property.split("\\.");
            String step = pathSteps[0];
            path = root.get(step);
            From lastFrom = root;

            for (int i = 1; i <= pathSteps.length - 1; i++) {
                if(path instanceof PluralAttributePath) {
                    PluralAttribute attr = ((PluralAttributePath) path).getAttribute();
                    Join join = getJoin(attr, lastFrom);
                    path = join.get(pathSteps[i]);
                    lastFrom = join;
                } else if(path instanceof SingularAttributePath) {
                    SingularAttribute attr = ((SingularAttributePath) path).getAttribute();
                    if(attr.getPersistentAttributeType() != Attribute.PersistentAttributeType.BASIC) {
                        Join join = lastFrom.join(attr, JoinType.LEFT);
                        path = join.get(pathSteps[i]);
                        lastFrom = join;
                    } else {
                        path = path.get(pathSteps[i]);
                    }
                }  else {
                    path = path.get(pathSteps[i]);
                }
            }
        } else {
            path = root.get(property);
        }
        return path;
    }

    private Join getJoin(PluralAttribute attr, From from) {
        switch (attr.getCollectionType()){
            case COLLECTION:
                return from.join((CollectionAttribute) attr);
            case SET:
                return from.join((SetAttribute) attr);
            case LIST:
                return from.join((ListAttribute) attr);
            case MAP:
                return from.join((MapAttribute) attr);
            default:
                return null;
        }
    }

Might be useful to add query.distinct(true); at the top of toPredicate method to avoid duplicate results.

[kumaravel95]

@perfectstrong @austalakov Thanks. Its working

[pankajm90]

@austalakov
I have used the above code in order to use it for nested objects but whenever I am using more than one nested attribute separated by a colon(;) as a predicate, hibernate is performing that many times inner join.
Example: [Below example is create just to explain the issue]
class Employee{
int id
String ename;
List dept;
}

class Department{
String id;
String name;
}

Predicate: ename=="test_name";dept.id=="10";dept.name="dname"
Query formed by hibernet:

           select 
                emp01.id, 
                emp01.ename 
           from 
                 Employee emp01
           inner join Department dept01 on
                 emp01.deptId = dept01.id
           inner join Department dept02 on // Why it is joining second time?
                emp01.deptId = dept02.id
           where
                emp01.ename=?
                and dept01.id=?
                and dept02=?  //This is the main issue-> it should be dept01=?

Please help me regarding this
Thanks

[pankajm90]

@austalakov
The above code for nested API will only work for a single attribute in the nested object. In order to add multiple attributes of the nested object as where clause. I have made a small change in code, in case it helps anyone:

private Join getJoin(PluralAttribute attr, From from) {
        final Set<?> joins = from.getJoins();
        for (Object object : joins) {
            Join<?, ?> join = (Join<?, ?>) object;
            if (join.getAttribute().getName().equals(attr.getName())) {
                return join;
            }
        }
        return createJoin(attr, from);
    }

private Join createJoin(PluralAttribute attr, From from) {
        switch (attr.getCollectionType()){
            case COLLECTION:
                return from.join((CollectionAttribute) attr);
            case SET:
                return from.join((SetAttribute) attr);
            case LIST:
                return from.join((ListAttribute) attr);
            case MAP:
                return from.join((MapAttribute) attr);
            default:
                return null;
        }
    }

[ikomangmahendra]

Many thanks @perfectstrong !!!

[torshid]

I came up with a much simpler solution which also prevents joining the same table multiple times in my alternative library.

https://github.com/turkraft/spring-filter/blob/caac7c5e3819600fb5aded37e0e85a7b8de5dca7/src/main/java/com/turkraft/springfilter/generator/ExpressionGeneratorUtils.java#L40-L95

public static Path<?> getDatabasePath(
  Root<?> table,
  Map<String, Join<?, ?>> joins,
  String fieldPath) {

  if (!fieldPath.contains(".")) {
    return table.get(fieldPath);
  }
  
  Path<?> path = table;
  From<?, ?> from = table;
  
  String[] fields = fieldPath.split("\\.");
  
  String chain = null;
  
  for (int i = 0; i < fields.length; i++) {
  
    String field = fields[i];
  
    path = from.get(field);
  
    if (chain == null) {
      chain = field;
    } else {
      chain += "." + field;
    }
  
    JoinType join = path instanceof PluralAttributePath ? JoinType.INNER
        : (path instanceof SingularAttributePath && ((SingularAttributePath<?>) path)
            .getAttribute().getPersistentAttributeType() != PersistentAttributeType.BASIC
                ? JoinType.LEFT
                : null);
  
    if (join != null && i < fields.length - 1) {
      if (!joins.containsKey(chain)) {
        joins.put(chain, from.join(field, join));
      }
      from = joins.get(chain);
    }
  
  }
  
  return path;

}

The library supports advanced search features, such as comparisons between fields (numbers, bools, enums, dates, ...), logical operations (including priority), functions, and more. You may check it here for those who lost hope in rsql.