Order by nested collection filtered count

Question

I'm trying to order by nested collection filtered count and couldn't figure out how to do it in OData.

I will use public OData API (from Microsoft) for demo to explain what I mean, no need for authorization

https://services.odata.org/TripPinRESTierService/

So we have People there

https://services.odata.org/TripPinRESTierService/People

These People have Trips

https://services.odata.org/TripPinRESTierService/People?$expand=Trips

If I want to filter Trips by Name, it's easy

https://services.odata.org/TripPinRESTierService/People?$expand=Trips($filter=Name eq 'Trip in Beijing')

If I want to order People by Trip count, it's easy as well

https://services.odata.org/TripPinRESTierService/People?$expand=Trips($count=true)&$orderby=Trips/$count desc

But I'm not able to order by filtered count of trips (for case when $expand=Trips($filter=Name eq 'Trip in Beijing'))

https://services.odata.org/TripPinRESTierService/People?$expand=Trips($count=true;$filter=Name eq 'Trip in Beijing')&$orderby=Trips/$count desc

Something like &$orderby=Trips($filter=Name eq 'Trip in Beijing')/$count desc or &$orderby=Trips/$count($filter=Name eq 'Trip in Beijing') desc doesn't work (gives server error "Could not find a property named 'Name' on type 'Trippin.Person'.").

Version of this URL to see minimum required data

https://services.odata.org/TripPinRESTierService/People?$expand=Trips($top=0;$count=true;$filter=Name eq 'Trip in Beijing')&$orderby=Trips/$count desc&$select=Trips

Update: I'm using Microsoft.AspNetCore.OData version 7.5.7 in my project which depends on Microsoft.OData.Core version 7.7.3 (It's not installed explicitly). Probably if Microsoft will upgrade version of OData in its API it's going to work as well.

user_10648805 · Answer

Update This is only supported for Microsoft.OData.Core versions >= 7.9.4 based on this pr.

You should be able repeat the same filter criteria in your $orderby that you have in your $expand to get what you are looking for.

/odata/People?$expand=Trips($count=true;$filter=Name eq 'Paris')&$orderby=Trips/$count($filter=Name eq 'Paris') desc

{
"@odata.context": "https://localhost:5001/odata/$metadata#People(Trips())",
"value": [
    {
        "Id": "d31713ca-236b-470f-bb9e-f5bd6fac3d8e",
        "Name": "Aaron",
        "Trips@odata.count": 2,
        "Trips": [
            {
                "Id": "741656bf-f5e6-4f18-855f-ca1b3c0eb478",
                "Name": "Paris"
            },
            {
                "Id": "741656bf-f5e6-4f18-855f-ca1b3c0eb478",
                "Name": "Paris"
            }
        ]
    },
    {
        "Id": "34c0ac10-1c0d-4a29-89ad-386a3fea893b",
        "Name": "Bob",
        "Trips@odata.count": 1,
        "Trips": [
            {
                "Id": "1b35a96d-ca98-438a-b63c-1478ae0661cb",
                "Name": "Paris"
            }
        ]
    },
    {
        "Id": "192dd197-6f25-4842-bb9f-207b5df221c2",
        "Name": "Jeff",
        "Trips@odata.count": 0,
        "Trips": []
    }
  ]
}

For comparison, here is the same dataset without the $filter.

/odata/People?$expand=Trips($count=true)&$orderby=Trips/$count desc

{
"@odata.context": "https://localhost:5001/odata/$metadata#People(Trips())",
"value": [
    {
        "Id": "192dd197-6f25-4842-bb9f-207b5df221c2",
        "Name": "Jeff",
        "Trips@odata.count": 3,
        "Trips": [
            {
                "Id": "4537521c-27a6-4fa4-8bb5-fd94937abd56",
                "Name": "Cleveland"
            },
            {
                "Id": "40431cba-0d8f-470a-9647-bdd99009737a",
                "Name": "Cleveland"
            },
            {
                "Id": "1269c3e3-b5ff-4a73-8201-5388d00ea744",
                "Name": "Cleveland"
            }
        ]
    },
    {
        "Id": "d31713ca-236b-470f-bb9e-f5bd6fac3d8e",
        "Name": "Aaron",
        "Trips@odata.count": 2,
        "Trips": [
            {
                "Id": "741656bf-f5e6-4f18-855f-ca1b3c0eb478",
                "Name": "Paris"
            },
            {
                "Id": "741656bf-f5e6-4f18-855f-ca1b3c0eb478",
                "Name": "Paris"
            }
        ]
    },
    {
        "Id": "34c0ac10-1c0d-4a29-89ad-386a3fea893b",
        "Name": "Bob",
        "Trips@odata.count": 1,
        "Trips": [
            {
                "Id": "1b35a96d-ca98-438a-b63c-1478ae0661cb",
                "Name": "Paris"
            }
        ]
    }
  ]
}

Order by nested collection filtered count

1 Answers