Move sql to inside forged

1 files changed, 27 insertions, 0 deletions

diff --git a/forged/sql/queries/groups.sql b/forged/sql/queries/groups.sql
new file mode 100644
index 0000000..07fe5e7
--- /dev/null
+++ b/forged/sql/queries/groups.sql
@@ -0,0 +1,27 @@
+-- name: GetGroupIDDescByPath :one
+WITH RECURSIVE group_path_cte AS (
+	SELECT
+		id,
+		parent_group,
+		name,
+		1 AS depth
+	FROM groups
+	WHERE name = ($1::text[])[1]
+		AND parent_group IS NULL
+
+	UNION ALL
+
+	SELECT
+		g.id,
+		g.parent_group,
+		g.name,
+		group_path_cte.depth + 1
+	FROM groups g
+	JOIN group_path_cte ON g.parent_group = group_path_cte.id
+	WHERE g.name = ($1::text[])[group_path_cte.depth + 1]
+		AND group_path_cte.depth + 1 <= cardinality($1::text[])
+)
+SELECT c.id, COALESCE(g.description, '')
+FROM group_path_cte c
+JOIN groups g ON g.id = c.id
+WHERE c.depth = cardinality($1::text[]);