Changes between Version 1 and Version 2 of AdvancedApplicationDevelopment

Timestamp:

09/21/25 21:31:12 (36 hours ago)

Author:

225144

Comment:

--

AdvancedApplicationDevelopment

@@ -3 +3 @@
 == Процедури
 == Погледи
+=== Поглед за прикажување на сите одговори на даден топик
+{{{
+create or replace view v_discussion_thread
+as
+with recursive
+    depth_table as
+        (select parent_id, id, 0 as depth
+         from discussion_thread
+         UNION ALL
+         select discuss.parent_id, dpth.id, dpth.depth + 1
+         from depth_table dpth
+                  join discussion_thread discuss
+                       on dpth.parent_id = discuss.id),
+    tmp as (select id, max(depth) as depth
+            from depth_table
+            group by id)
+select d.id as id, t.user_id as user_id, d.depth as depth, d1.parent_id as parent_id, t.created_at as "created_at"
+from tmp d
+         join depth_table d1
+              on d.id = d1.id and d1.depth = d.depth
+         join thread t
+              on t.id = d.id;
+}}}
+
+=== Поглед за прикажување на пермисиите за дадена улога во за сите канали во даден проект.
+{{{
+CREATE OR REPLACE VIEW role_channel_permissions AS
+SELECT
+    c.project_resource_id,
+    c.name,
+    pr.id as role_id,
+    COALESCE(
+                    STRING_AGG(
+                    DISTINCT rp.permission_name, ',' ORDER BY rp.permission_name
+                              ) FILTER (
+                        WHERE
+                        (pr.override_type = 'INCLUDE' AND rpo.project_resource_id IS NOT NULL)
+                            OR
+                        (pr.override_type = 'EXCLUDE' AND rpo.project_resource_id IS NULL)
+                        ),
+                    ''
+    ) AS permissions
+FROM channel c
+         JOIN project_role pr
+              ON pr.project_id = c.project_id
+         LEFT JOIN role_permissions rp
+                   ON rp.role_id = pr.id
+                       AND rp.permission_name IN ('READ','WRITE')
+         LEFT JOIN role_permissions_overrides rpo
+                   ON rpo.role_id = pr.id
+                       AND rpo.permission_name = rp.permission_name
+                       AND rpo.project_resource_id = c.project_resource_id
+GROUP BY c.project_resource_id, c.name,pr.id
+}}}