Changes between Version 7 and Version 8 of AdvancedReports

Timestamp:

09/26/25 19:41:50 (5 weeks ago)

Author:

225144

Comment:

--

AdvancedReports

@@ -37 +37 @@
 }}}
 
-== Приказ на сите topics или прокети кои ги задоволовуаат дадените критериуми
+== Генерално филтрирање на сите topics или проекти
 
 {{{
@@ -47 +47 @@
                     AND (CAST(:tags as varchar) IS NULL OR t.tags @> string_to_array(lower(:tags),',')::varchar[])
 }}}
+
+== Приказ на сите дискусии во рамки на даден topic, подредени според формула за популарност
+
+{{{
+WITH RECURSIVE tree AS (
+    SELECT
+        t.id,
+        content,
+        created_at,
+        t.is_created_by,
+        0::numeric as "level",
+        -1  as "parent"
+    FROM topic_thread tt
+    join thread t
+    on t.id = tt.id
+    where t.id = :topicId
+
+    UNION ALL
+
+    SELECT child.id,
+           child.content,
+           child.created_at,
+           child.is_created_by,
+           parent.level+1 as level,
+           dt.contained_in as parent
+    FROM thread child
+    join discussion_thread dt
+    on child.id = dt.id
+    JOIN tree parent
+    ON dt.contained_in = parent.id
+),
+thread_data as (SELECT tc.id as "id",
+                       tc.content as "content",
+                       tc.created_at as "created_at",
+                       tc.level as "level",
+                       tc.parent as "parent_id",
+                       u.id as userId,
+                       u.username,
+                       (SELECT COUNT(*)
+                        FROM tree row
+                        WHERE
+                            tc.id =
+                            row.parent) AS numReplies,
+                       (SELECT COALESCE(
+                                       array_agg(l.user_id),
+                                       '{}')
+                        FROM likes l
+                        WHERE
+                            l.thread_id =
+                            tc.id)      AS likedByUserIds
+                FROM tree tc
+                         join users u
+                              on u.id =
+                                 tc.is_created_by)
+SELECT
+    id,
+    content,
+    created_at as createdAt,
+    level,
+    parent_id as parentId,
+    userId,
+    username,
+    numReplies,
+    likedByUserIds
+FROM thread_data td
+ORDER BY
+    level,
+    0.3 * numReplies + 0.7 * cardinality(likedByUserIds)
+DESC ;
+}}}
+
+* Прашаликот за секој discussion ги враќа user_id што поставиле like како низа, со цел да може потоа во апликацијата да се прикаже точно кој корисник поставил like.