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.
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",
"[email protected]": 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",
"[email protected]": 1,
"Trips": [
{
"Id": "1b35a96d-ca98-438a-b63c-1478ae0661cb",
"Name": "Paris"
}
]
},
{
"Id": "192dd197-6f25-4842-bb9f-207b5df221c2",
"Name": "Jeff",
"[email protected]": 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",
"[email protected]": 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",
"[email protected]": 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",
"[email protected]": 1,
"Trips": [
{
"Id": "1b35a96d-ca98-438a-b63c-1478ae0661cb",
"Name": "Paris"
}
]
}
]
}