Погледи

Поглед за прикажување на сите одговори на даден топик

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

Поглед за групирање на топици и проекти за прикажување на почетната страница

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;