Skip to content

WhyGeZeroNotSupported.adoc

Latest commit

 

History

History
86 lines (83 loc) · 3.61 KB

WhyGeZeroNotSupported.adoc

File metadata and controls

86 lines (83 loc) · 3.61 KB

Why are navigation count queries using greater or equal not supported?

In OData queries a client can make use of the $count segment within a $filter expression to limit the items returned based on the exact count of related entities at collection-valued property.

A small example shall demonstrate this. The example uses an entity, AdministrativeDivision, from the test model. AdministrativeDivisions is a hierarchy of subnational entities.

To find all the divisions that have two sub-divisions, the following GET request can be used:

.../AdministrativeDivisions?$filter=Children/$count eq 2

This leads to the following SQL query:

SELECT
	DISTINCT t0."Population",
	t0."Area",
	t0."DivisionCode",
	t0."ParentCodeID",
	t0."AlternativeCode",
	t0."CountryISOCode",
	t0."CodePublisher",
	t0."ParentDivisionCode",
	t0."CodeID"
	FROM "OLINGO"."AdministrativeDivision" t0
	WHERE (EXISTS (  --(1)
		SELECT t1."CodePublisher" --(2)
			FROM "OLINGO"."AdministrativeDivision" t1
			WHERE (((t1."CodePublisher" = t0."CodePublisher")
			AND (t1."ParentCodeID" = t0."CodeID"))
			AND (t1."ParentDivisionCode" = t0."DivisionCode"))
			GROUP BY t1."CodePublisher", t1."ParentCodeID", t1."ParentDivisionCode"
			HAVING (COUNT(t1."DivisionCode") = 2))) --(3)

The rows having 2 children are those where a sub-query (2) result EXISTS (1). The restriction of the sub-query is done by a HAVING clause (3). Unfortunately this construct is not able to compare with 0. E.g. to find all leaves of the division hierarchy, lowest level of divisions, the following GET request can be used:

.../AdministrativeDivisions?$filter=Children/$count eq 0

In case the request would be converted into the following SQL:

SELECT
	DISTINCT t0."Population",
	t0."Area",
	t0."DivisionCode",
	t0."ParentCodeID",
	t0."AlternativeCode",
	t0."CountryISOCode",
	t0."CodePublisher",
	t0."ParentDivisionCode",
	t0."CodeID"
	FROM "OLINGO"."AdministrativeDivision" t0
	WHERE (EXISTS (
		SELECT t1."CodePublisher"
			FROM "OLINGO"."AdministrativeDivision" t1
			WHERE (((t1."CodePublisher" = t0."CodePublisher")
			AND (t1."ParentCodeID" = t0."CodeID"))
			AND (t1."ParentDivisionCode" = t0."DivisionCode"))
			GROUP BY t1."CodePublisher", t1."ParentCodeID", t1."ParentDivisionCode"
			HAVING (COUNT(t1."DivisionCode") = 0)))

the query would not find anything, as the sub-query does not return a result. The restriction has to be converted. Instead of using EXIST is is necessary to use NOT EXIST (4) together with a comparison to not equal zero (5):

SELECT
	DISTINCT t0."Population",
	t0."Area",
	t0."DivisionCode",
	t0."ParentCodeID",
	t0."AlternativeCode",
	t0."CountryISOCode",
	t0."CodePublisher",
	t0."ParentDivisionCode",
	t0."CodeID"
	FROM "OLINGO"."AdministrativeDivision" t0
	WHERE NOT (EXISTS ( --(4)
		SELECT t1."CodePublisher"
			FROM "OLINGO"."AdministrativeDivision" t1
			WHERE (((t1."CodePublisher" = t0."CodePublisher")
			AND (t1."ParentCodeID" = t0."CodeID"))
			AND (t1."ParentDivisionCode" = t0."DivisionCode"))
			GROUP BY t1."CodePublisher", t1."ParentCodeID", t1."ParentDivisionCode"
			HAVING (COUNT(t1."DivisionCode") <> 0))) --(5)

In case a client requests $count greater or equal 0, it would mean to create a sub-query with NOT EXISTS and a sub-query with EXISTS. This does not make sense, as in fact such a request does not restrict the result at all.