Usage of filter at grand child level ODATA 4

[RahulYerrabandi76]

Hi All,

We have a OData V4 service with 3 CDS view entities.  SalesOrderHeader(Parent) -> OrderItem(Child) -> OrderSchedule(GrandChild).

For one scenario we need to filter with a field in Order Schedule. For our example, let us assume that I want all the orders with SOLDTOPARTY = '654' and only those items who has a schedule line number 3.

Lets just say in the system we have 2 orders 

Order1   SOLDTOPARTY = '123'

     Item1

          Schedule line = 1

          Schedule line = 2

      Item2

          Schedule line = 1

          Schedule line = 2

          Schedule line = 3

Order2   SOLDTOPARTY = '654'

    Item1

        Schedule line = 1

        Schedule line = 2

    Item2

        Schedule line = 1

        Schedule line = 2

       Schedule line = 3

Desired output in OData call

Order2   SOLDTOPARTY = '654'

    Item2

        Schedule line = 3

We have used the below URL to test

/sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?$expand=_Item($expand=_Schedule($filter=ScheduleLine eq '3'))&$filter=(SoldToParty eq '654') and _Item/any(r: r/_Schedule/any(t: t/ScheduleLine eq '3'))

Below is the output that we got. As you can see below Item1 of Order2 is coming in the output although the ScheduleLine is empty. 

Order2   SOLDTOPARTY = '654'

    Item1

    Item2

        Schedule line = 3

What we want is, if all the schedule lines are not matching the criteria the corresponding Item also should not be in the output. But as you can see above, Item1 is included even though all the schedule lines are filtered.

Thank you in advance for trying to look into it.

[RalfHandl · Accepted solution]

Filters within $expand work independent of the filter on the root.

Your example filters on root, and the $expand only filters on schedule lines, there is no filter on items.

Adding a filter in $expand for the items should return the desired result:

/sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?$expand=_Item($filter=_Schedule/any(t: t/ScheduleLine eq '3');$expand=_Schedule($filter=ScheduleLine eq '3'))&$filter=(SoldToParty eq '654') and _Item/any(r: r/_Schedule/any(t: t/ScheduleLine eq '3'))

[RahulYerrabandi76 · Accepted solution]

Thank you RalfHandl. It worked and I was able to get the right data.