Cannot search on 'null' ids in a manyToOne association

[cyrilmata]

Hi,

First of all, thanks for your great job on that library. It's very useful.

I encountered one unexpected behavior when trying to use the =isnull= operator on a manyToOne association.

I want to find all users with a NULL specialite.

Here are my JPA entities

// UserEntity
@Entity
@Table(name = "domain_user")
public class UserEntity {

{...}

@ManyToOne
@JoinColumn(name = "specialite_id")
private SpecialiteEntity specialite;
}

// SpecialiteEntity
@Entity
@Table(name = "domain_specialite")
public class SpecialiteEntity {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
Long id

{...}
}

My RSQL query looks like that
specialite.id=isnull=

I get no result at all.

I checked the generated SQL request, and there is something wrong

select * from domain_user user0_ inner join domain_specialite specialite1_ on user0_.specialite_id=specialite1_.id where specialite1_.id is null

Because of the where clause on the joined entity, of course there is no specialite with null id in the specialite table

In that particular case, i don't even need any join, the following request would be perfect

select * from domain_user user0_ where user0_.specialite_id is null

To avoid the join, I also tried to not use the 'id' property in the RSQL query

specialite=isnull=

But I got a NullPointerException when you try to resolve the attribute name from the ManyToOne association.

Do you have any workaround to do that ? Maybe i'm doing something wrong with the usage of your library, I hope you could help me :)

Thank you very much,

Cheers,
Cyril

[perplexhub]

could you try specialite=isnull='' ?

[cyrilmata]

Hi,

Thanks for your reply,

Like I told you in my previous message, i already tried specialite=isnull= but i get a NullPointerException in the method

findPropertyPath of the class RSQLJPAPredicateConverter.

On line 109 :
accessControl(type, attribute.getName());

As i do not specify any attribute, the attribute property is null.

[perplexhub]

It is not supported. You can add a field specialiteId (specialite_id) in UserEntity, then you can query by user.specialiteId=isnull=''. Thanks.

[cyrilmata]

Hi,
Thanks for your reply, i found a very quick workaround, better than adding a specialiteId in SpecialiteEntity, which is :

public List<User> listUserWithNullSpecialite(String rsqlQuery) {
   Specification<User> spec = RSQLJPASupport.toSpecification(rsqlQuery);
   specification = specification.and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.isNull(root.get("specialite")));
   return this.repository.findAll(specification);
}

Maybe you could implement that in the library :)

Thank you anyway,

Cheers,

[perplexhub]

You can try this feature https://github.com/perplexhub/rsql-jpa-specification#custom-operator--predicate to customize it. Thanks.

[AndreiCatinas]

Hi,

I have a similar issue...sorta.
An entity with a OneToMany relationship.

@Entity
@Table(name = "material")
public class MaterialEntity {

{...}

@OneToMany
private Set<PictureEntity> pictures;
}

I'm trying to find all the materials without any pictures. So basically a null set of pictures. Is there any way of doing this?
I tried using a custom operator but it never gets that far because a NPE happens here as cyrilmata mentioned:

On line 109 :
accessControl(type, attribute.getName());

This happens because attribute is null if I try something like "filter=pictures=empty=''" with my custom operator.

[perplexhub]

Please try version 5.0.12. I 've added NPE check.

[AndreiCatinas]

Hi,

Thanks a lot for the quick reply and the fix, I really apreciate it.

I tried the new version, and now the NPE moved to
io.github.perplexhub.rsql.RSQLJPAPredicateConverter#visit(cz.jirutka.rsql.parser.ast.ComparisonNode, javax.persistence.criteria.From)

It still tries to get an attribute here:

Attribute attribute = holder.getAttribute();
Class type = attribute.getJavaType();

So the execution stops before reaching the custom predicates.

[perplexhub]

Please upgrade to v5.0.13. Please refer to io.github.perplexhub.rsql.RSQLJPASupportTest#testCustomPredicateIsNull for details, thanks!

		String rsql = "city=notAssigned=''";
		RSQLCustomPredicate<String> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=notAssigned="), String.class, input -> {
			return input.getCriteriaBuilder().isNull(input.getRoot().get("city"));
		});
		List<User> users = userRepository.findAll(toSpecification(rsql, Arrays.asList(customPredicate)));

[AndreiCatinas]

Hi,

Thanks a lot for the fix and your help. This should work but I think there's something about the way I'm mapping my entities. I'll figure something out. Thanks again for your help and for this library. It saved me a lot of trouble. :)

Best of luck!

[AndreiCatinas]

Okay, I managed to find a solution to my problem and I'm gonna post it here in case anyone needs something similar.
I have a Parent entity which has a OneToMany relationship with a Child entity. Like this:

@Entity
@Table(name = "parent")
public class ParentEntity {
    
    {...}

    @OneToMany
    @JoinColumn(name = "parent_id")
    private Set<ChildEntity> children;

    {...}

}

@Entity
@Table(name = "child")
public class ChildEntity {

    {...}

    @Column(name = "parent_id")
    private Long parentId;

    {...}
}

I need to filter all parent entities which either have children or don't, and here is my custom predicate:

        RSQLCustomPredicate<Boolean> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=notAssigned="), Boolean.class, new Function<RSQLCustomPredicateInput, Predicate>() {
            @Override
            public Predicate apply(RSQLCustomPredicateInput input) {
                boolean isEmpty = (boolean) input.getArguments().get(0);
                Path<Collection<?>> path = (Path<Collection<?>>) input.getPath();
                if (isEmpty) {
                    return input.getCriteriaBuilder().isEmpty(path);
                } else {
                    return input.getCriteriaBuilder().isNotEmpty(path);
                }
            }
        });

Now I can find all Parent entities who don't have any Child entities like this:

search=?children=notAssigned='true'

[basven]

This will not work for deeper relations like parent.children.grandchildren. I think because the JPA spec states that that behavior is undefined (and I see a hibernate exception when I try to do use either the isEmpty or size methods on those). My workaround for now was to use the hibernate specific @formula annotation to count the # of grand children and filter on that.

[Mintas]

the issue can be easily fixed by replacing empty line # 88 with the following code:
attribute = classMetadata.getAttribute(property);
at

rsql-jpa-specification/rsql-jpa/src/main/java/io/github/perplexhub/rsql/RSQLJPAPredicateConverter.java

Line 88 in 4e7cac1

is there any good reason for this code not to be there?

[perplexhub]

Caused by: java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [company] on this ManagedType [io.github.perplexhub.rsql.model.Company]
at org.hibernate.metamodel.internal.AbstractManagedType.checkNotNull(AbstractManagedType.java:128)
at org.hibernate.metamodel.internal.AbstractManagedType.getAttribute(AbstractManagedType.java:113)
at io.github.perplexhub.rsql.RSQLJPAPredicateConverter.findPropertyPath(RSQLJPAPredicateConverter.java:88)