balena-io-modules / lf-to-abstract-sql Goto Github PK

It is necessary that each plan that includes a plan feature that provides a feature1, includes at most one plan feature that provides the feature1

SELECT NOT EXISTS (
	SELECT 1
	FROM "plan" AS "plan.0",
			"plan feature" AS "plan feature.1",
			"feature" AS "feature.2",
			"plan-includes-plan feature" AS "plan.0-includes-plan feature.1"
	WHERE "plan feature.1"."provides-feature" = "feature.2"."id"
	AND "plan.0-includes-plan feature.1"."plan" = "plan.0"."id"
	AND "plan.0-includes-plan feature.1"."plan feature" = "plan feature.1"."id"
	AND (
			SELECT COUNT(*)
			FROM "plan feature" AS "plan feature.3",
					"plan-includes-plan feature" AS "plan.0-includes-plan feature.3"
			WHERE "plan feature.3"."provides-feature" = "feature.2"."id"
			AND "plan.0-includes-plan feature.3"."plan" = "plan.0"."id"
			AND "plan.0-includes-plan feature.3"."plan feature" = "plan feature.3"."id"
	) >= 2
 ) AS "result";