Changes between Version 1 and Version 2 of Views

Timestamp:

09/26/25 12:44:56 (3 weeks ago)

Author:

226026

Comment:

--

Views

@@ -54 +54 @@
 GROUP BY c.project_resource_id, c.name,pr.id
 }}}
+
+== Поглед за групирање на топици и проекти за прикажување на почетната страница 
+
+{{{ 
+create or replace view v_named_threads as
+WITH topics_projects AS (SELECT pr.id,
+                                pr.title,
+                                'projects'::text AS "type"
+                         FROM project_thread pr
+                         UNION
+                         SELECT topic_thread.id,
+                                topic_thread.title,
+                                'topics'::text AS "type"
+                         FROM topic_thread),
+     topics_projects_threads AS (SELECT t.id,
+                                        t.content,
+                                        tp.type,
+                                        tp.title,
+                                        u.username,
+                                        u.id        AS user_id,
+                                        t.created_at
+                                 FROM thread t
+                                          JOIN users u ON u.id = t.is_created_by
+                                          JOIN topics_projects tp ON tp.id = t.id)
+        ,
+     named_threads_tags as (select tt.thread_id           as "id",
+                                   array_agg(tt.tag_name) as "tags"
+                            from tag_assigned_to_thread tt
+                            where tt.thread_id in (select id from topics_projects_threads)
+                            group by tt.thread_id)
+SELECT tpt.id,
+       tpt.content,
+       tpt.title,
+       tpt.type,
+       tpt.username,
+       tpt.user_id,
+       tpt.created_at,
+       coalesce((select ntt.tags as "tags"
+                 from named_threads_tags ntt
+                 where ntt.id = tpt.id),'{}') as "tags"
+FROM topics_projects_threads tpt;
+}}}