From a85d1d8d6b25e13500b0895209b6343f0b2bc435 Mon Sep 17 00:00:00 2001
From: Runxi Yu <me@runxiyu.org>
Date: Mon, 18 Aug 2025 01:18:48 +0800
Subject: Add sqlc

---
 sql/queries/groups.sql | 27 +++++++++++++++++++++++++++
 1 file changed, 27 insertions(+)
 create mode 100644 sql/queries/groups.sql

(limited to 'sql')

diff --git a/sql/queries/groups.sql b/sql/queries/groups.sql
new file mode 100644
index 0000000..07fe5e7
--- /dev/null
+++ b/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[]);
-- 
cgit v1.2.3