I am trying to compose a OData URL which will selects from a collection with a filter on a related collection with a one-to-many relationship. The filter requires the related collections items to all have a specific value for some property.
To illustrate this issue, I wrote a similar URL for the Northwind sample OData service. This query should select all customers for whom all orders have been processed by the same employee. http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$expand=Orders&$filter=Orders/all(o: o/EmployeeID eq 4)&$select=CustomerID,Orders/OrderID,Orders/EmployeeID
This gives:
{"odata.metadata":"http://services.odata.org/V3/Northwind/Northwind.svc/$metadata#Customers&$select=CustomerID,Orders/OrderID,Orders/EmployeeID","value":
[{"Orders":[{"OrderID":10259,"EmployeeID":4}],"CustomerID":"CENTC"}
,{"Orders":[],"CustomerID":"FISSA"}
,{"Orders":[],"CustomerID":"PARIS"}]}
The first item has indeed all (in this case, just one) orders processed by employee 4. For the second and third no order items are in the result set. On further inspection, http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$expand=Orders&$filter=(CustomerID eq 'FISSA')&$select=CustomerID,Orders/OrderID,Orders/EmployeeID reveals that 'FISSA' does indeed not have any orders (an this is also true for 'PARIS').
Maybe one could reason that, since 'FISSA' does not have any orders disobeying the filter predicate, the predicate holds. In fact, this might be the way this query is resolved in SQL:
select c.CustomerID
from Customers c
where c.CustomerID not in (select CustomerID from Orders o where o.EmployeeID != 4)
returning the same 3 customers, but joining in the Orders table would solve this:
select c.CustomerID, o.OrderID, o.EmployeeID
from Customers c
join Orders o
on c.CustomerID = o.CustomerID
where c.CustomerID not in (select CustomerID from Orders o where o.EmployeeID != 4)
returning just 'CENTC'.
I think that the filter predicate is undetermined for 'FISSA' and, following the rules of three-valued-logic, 'FISSA' and 'PARIS' should not be in the result set. So, I consider this a bug in this OData implementation.
Does anybody know the right OData request query to leave out customers with no orders?
Adding any to the $filter you can remove any null matches: Orders/any(o:o/EmployeeID ne null)
http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$expand=Orders&$select=CustomerID,Orders/OrderID,Orders/EmployeeID&$filter=Orders/all(o: o/EmployeeID eq 4) and Orders/any(o:o/EmployeeID ne null)