How to filter collection?

[mrnickel]

I'm using the generator to generate all my types.

One of the properties of my types is an array of enum values.

interface:

selectablePartStatuses: SelectablePartStatuses[];

namespace:

export const SELECTABLE_PART_STATUSES = _fieldBuilder.buildCollectionField('selectablePartStatuses', SelectablePartStatuses, false);

Everything is properly generated.

I'm trying to use the requestBuilder to create any ANY query for that property but I cannot for the life of me figure out how to do it.

Thanks so much for your help, and for the creation of this library. It's fantastic!

[marikaner]

@mrnickel Thank you for your appreciation.

Can you express what you are trying to do as an OData filter string? I am aware of these filter functions by OData for collections: here and here. Here is a list of functions the SDK supports and here you can find a few examples on how to use them.

Examples:

import { filterFunctionsV4 } from '@sap-cloud-sdk/core';

// hasSubset
MyEntity.requestBuilder()
  .getAll()
  .filter(filterFunctionsV4.hasSubset(MyEntity.COLLECTION_PROPERTY, [1]));

// length
MyEntity.requestBuilder()
  .getAll()
  .filter(filterFunctionsV4.length(MyEntity.COLLECTION_PROPERTY).equals(3));

Hope that helps?

[mrnickel]

My odata server doesn't support hasSubset.

What I'm trying to do is compose a query that looks like:

(selectablePartStatuses/any(s: s eq 'Defective' or s eq 'Good'))

[mrnickel]

For example, looking at the lamda any example, you can filter by a navigation property with any

But what if I wanted to search by the EmailAddress

Something like:

(Person.Emails/any(s: s eq '[email protected]' or s eq '[email protected]'))

[jjtang1985]

Hi @mrnickel ,

It seems you showed an example about the TripPinService. I think the lambda expression cannot be used with a collection field.

I built a similar request below:

https://services.odata.org/TripPinRESTierService/People('russellwhyte')?$expand=Friends&$filter=Friends/Emails/any(e: e eq '[email protected]')

If you try it, you'll see an error:

{"error":{"code":"","message":"A node of this kind requires the associated property to be a structural, non-collection type, but property 'Emails' is a collection."}}

Here is the link to the original OData V4 documentation, where it says:

OData defines two operators that evaluate a Boolean expression on a collection. Both must be prepended with a navigation path that identifies a collection.

[mrnickel]

@jjtang1985 thanks for that clarification.

How do you suggest that I filter based on a collection that's not a navigation.

For example, a type that contains an array of strings?

type Part {
  id: number,
  code: string,
  partStatuses: string[]
}

[mrnickel]

@jjtang1985 further, the following query does work against their service:

https://services.odata.org/TripPinRESTierService/(S(ts5bnmayur22pucw5llcpuxm))/People('russellwhyte')?$filter=Emails/any(e: e eq '[email protected]' or e eq '[email protected]' or e eq '[email protected]')

(edited to make the URL more readable)

[jjtang1985]

@mrnickel ,

Thanks for your working example. I guess this is not supported for the time being. I'll discuss it with the team and get back to you soon.

Could you please share your project status (e.g., PoC or Go Live) and your timeline?

For now, I know your odata system does not support hasSubset function. What about this filter function contains, which should work with both string and collection. Could you please check your odata system and share the results?

[mrnickel]

@jjtang1985 This is a Live project. We're working on writing some more advanced queries to expose some more complicated UI in the application.

Unfortunately it doesn't look like our server supports contains.

[jjtang1985]

Hi @mrnickel ,

Could you please share your time line for the queries about filtering collections? Also, have you tried your odata system, so a filter like below works?

$filter=partStatuses/any(e: e eq 'status1' or e eq 'status2')

[mrnickel]

@jjtang1985

Like every project, everything is urgent. We're knee deep in the development right now so it looks like we're going to have to hand roll our complex queries for the time being.

Yes, I have tried our odata system with the above query, works like a charm :-)

Thanks for your help

[jjtang1985]

@mrnickel ,

As a work around, you can use the addCustomQueryParameters like the example below:

People.requestBuilder()
      .getAll()
      .addCustomQueryParameters({'$filter': 'Emails/any(e:%20e%20eq%20%[email protected]%27)'})
      .url(destination);

Please note, you have to handle the whole $filter and the url encoding. Please let me know your feedback.

Thank you.

[mrnickel]

Looks like that would work, however we'd want to append to the built up filter we've already created.

i.e.

People.requestBuilder()
  .getAll()
  .filter(
    .. various filters here ..
  )
  .addCustomerQueryParameters({'$filter': '... whatever else here...'});
  .url(destination)

However that would overwrite the existing filters.

Would be nice to appendCustomQueryParameters

[jjtang1985]

Hi @mrnickel ,

As a flexible/powerful workaround method, the addCustomeQueryParameters will overwrite existing query parameters as intended. Another workaround is:

// This is an internal api used by us. You can build your filter the same way like the request builder.
// Switch to oDataUriV4, if your service is an odata version 4 service instead of odata version2
    const filterTyped = oDataUriV2.getFilter(People.FIRST_NAME.equals('abc'), People).filter;
// You can then merge the previous filter and any string like below
    const filter = `${filterTyped}&Emails/any(e:%20e%20eq%20'[email protected]'))`;
// Eventually, you can set the merged filter.
    const people = await People.requestBuilder()
      .getAll()
      .addCustomQueryParameters({'$filter': filter})
      .url(destination);

Please note:

• This is an undocumented internal API, using it for a productive app might be risky.
• getFilter handles encoding automatically.

Please let me know your feedback, if possible.

[artemkovalyov]

Hey, @mrnickel

I'm a PO of the SAP Cloud SDK for JS. I have a couple of quick questions for you as you seem to be an SDK power user:

1. We are soon to release a new major version of the SDK.

• Would you be interested to join the beta testers squad?
• The update should require a minimum time and the team will help beta testers with 1st priority.

2. Would you be interested to join a feedback call with the SDK team? If you have any points to share with us, feel free to approach me in person.

We'd also love to hear if the workaround suggested by @jjtang1985 worked for you!

[jjtang1985]

I'll close this ticket. Please consider the workaround mentioned.