In today's episode, there was a question at around 58:30 about applying the system query option $filter
to multiple levels in an $expand
request, and whether it is possible. The context was this OData resource URL:
http://localhost:4004/northwind-model/Categories?$expand=Products($filter=Discontinued eq true)
and the wording of the question was: "can we combine filter on both Categories and Products entity set?".
The answer is: yes.
Here's an example (based on the CAP-based OData service defined in this repository) that uses the Suppliers
entity set instead of the Categories
entity set simply because there's more data for us with which to find a meaningful filter example.
If we want to retrieve the suppliers in the UK, and, alongside the supplier information itself, want a list of their products, but only products that have low stock (say, 15 units or fewer), we could ask for all of this in a single OData query operation thus:
http://localhost:4004/northwind-model/Suppliers
?$filter=Country eq 'UK'
&$expand=Products($filter=UnitsInStock le 15)
There are two filters at work here, one on the Suppliers
entity set, and one on the Products
entity set (remember that there's a managed association in the schema linking a supplier to their products).
This results in a JSON representation of that resource, that looks like this:
{
"@odata.context": "$metadata#Suppliers(Products())",
"value": [
{
"SupplierID": 1,
"CompanyName": "Exotic Liquids",
"ContactName": "Charlotte Cooper",
"ContactTitle": "Purchasing Manager",
"Address": "49 Gilbert St.",
"City": "London",
"Region": "NULL",
"PostalCode": "EC1 4SD",
"Country": "UK",
"Phone": "(171) 555-2222",
"Fax": "NULL",
"HomePage": "NULL",
"Products": [
{
"ProductID": 3,
"ProductName": "Aniseed Syrup",
"QuantityPerUnit": "12 - 550 ml bottles",
"UnitPrice": 10,
"Category_CategoryID": 2,
"Supplier_SupplierID": 1,
"UnitsInStock": 13,
"UnitsOnOrder": 70,
"ReorderLevel": 25,
"Discontinued": false
}
]
},
{
"SupplierID": 8,
"CompanyName": "Specialty Biscuits Ltd.",
"ContactName": "Peter Wilson",
"ContactTitle": "Sales Representative",
"Address": "29 King's Way",
"City": "Manchester",
"Region": "NULL",
"PostalCode": "M14 GSD",
"Country": "UK",
"Phone": "(161) 555-4448",
"Fax": "NULL",
"HomePage": "NULL",
"Products": [
{
"ProductID": 21,
"ProductName": "Sir Rodney's Scones",
"QuantityPerUnit": "24 pkgs. x 4 pieces",
"UnitPrice": 10,
"Category_CategoryID": 3,
"Supplier_SupplierID": 8,
"UnitsInStock": 3,
"UnitsOnOrder": 40,
"ReorderLevel": 5,
"Discontinued": false
},
{
"ProductID": 68,
"ProductName": "Scottish Longbreads",
"QuantityPerUnit": "10 boxes x 8 pieces",
"UnitPrice": 12.5,
"Category_CategoryID": 3,
"Supplier_SupplierID": 8,
"UnitsInStock": 6,
"UnitsOnOrder": 10,
"ReorderLevel": 15,
"Discontinued": false
}
]
}
]
}