How to Filter On Null

[SpaceCondor]

Discussed in #424

^{Originally posted by SpaceCondor August 17, 2021}
Is it possible to filter on a value being 'null'? I am trying to filter on a particular String being null.

For example:

.filter(Order.COMMENTS.equalTo(null))

[cschubertcs]

Hi @SpaceCondor,

in the OData client of the Cloud SDK it's possible to create such a filter the following way:

ValueBoolean filterFunction = FieldReference.of(BusinessPartner.FIRST_NAME.getFieldName()).equalTo(Expressions.Operand.NULL)

If you are using our OData V2 VDM you can then also consumer that filter the following way:

new DefaultBusinessPartnerService()
            .getAllBusinessPartner()
            .filter(new UncheckedFilterExpression<>(filterFunction));

This is, however, only a workaround and will be improved on in the future.

For our OData V4 VDM there is no such workaround possible at the moment.
Also that is a thing we want to improve in the future.

If that already answers your question, feel free to close the issue, or let me know what I missed.

Greetings
Chris

[SpaceCondor]

Thank you! Unfortunately we are using V4 so that workaround won't work. Glad to know it is on your radar!

[newtork]

Is "COMMENTS" a 1:n navigation property? Have you checked whether this filter expression works for the specific OData service?
Is this considered OData V2 or V4? For the latter, there's the Collection#length filter method, but some OData services may not support this filter expression.

[newtork]

@SpaceCondor , my suggestion would look like the following - have you tried...?

FilterableBoolean<Order> noCommentsFilter = Order.COMMENTS.length().equalTo(0);

Alternatively, if you want to enforce the null comparison, that would be a bit tedious with today's public API on OData V4 VDM:

FieldUntyped fieldUntyped = FieldReference.of(Order.COMMENTS.getFieldName());
ValueBoolean.Expression fieldExpression = (ValueBoolean.Expression) fieldUntyped.equalTo((Object) null);
FilterableBoolean<Order> noCommentsFilter = new FilterableBoolean.Expression<>(fieldExpression, Order.class);

Or another potential solution is to use our OData Generic Client for OData experts, but it may not take advantage of your generated OData API.

Please understand we're still investigating the null comparison feature for multi-value entity fields. So far we have not seen an OData service handling X eq null for X being a collection. Can you give us an example / a proof showing that the comparison is working for you? And the FieldX.length().equalTo(0) is not?

[SpaceCondor]

Hey @newtork, thanks for the suggestion! I actually had already tried that, but wasn't successful.

In this example, COMMENTS is a string, not a collection. I will play around with the generic client to see if I can get something workable in the meantime.

[newtork]

@SpaceCondor wrote:

COMMENTS is a string, not a collection.

I'm sorry, I misinterpreted your question then. Please use the following simple expression:

FilterableBoolean<Order> noCommentsFilter = Order.COMMENTS.equalTo((String) null);

Your IDE may complain the function argument is annotated as non-null. We fixed the annotation to nullable for next relaese. But it should work for you already.

[newtork]

As of 3.53.0 we support the following helper filter operators:

• Entity.FIELD_NAME.equalToNull() - OData V4 VDM / Generic OData Client
• Entity.FIELD_NAME.notEqualToNull() - OData V4 VDM / Generic OData Client
• Entity.FIELD_NAME.eqNull() - OData V2 VDM
• Entity.FIELD_NAME.neNull() - OData V2 VDM