Напредни извештаи од базата (SQL)

Проверка дали даден корисник поседува дадена пермисија за даден ресурс

SELECT COALESCE(
                  EXISTS (SELECT 1
                          FROM users_project_roles upr
                                   JOIN project_role pr
                               ON upr.role_id = pr.id
                                   JOIN role_permissions rp
                                    ON pr.id = rp.role_id
                                   LEFT JOIN role_permissions_overrides rpo
                                             ON pr.id = rpo.role_id
                                                 AND rpo.permission_name = rp.permission_name
                                                 AND rpo.project_resource_id = :resourceId
   
                          WHERE upr.user_id = :userId
                            AND pr.project_id = :projectId
                            AND rp.permission_name = :permissionName
                            AND (
                              (pr.override_type = 'INCLUDE' AND rpo.project_resource_id IS NOT NULL)
                                  OR (pr.override_type = 'EXCLUDE' AND rpo.project_resource_id IS NULL)
                              )), FALSE
          ) AS has_access;

Приказ на пријави според во даден топик според даден статус и филтрација на пријавите за секој корисник според новоста

   select *
            from report r
            join submission s
            on s.id = r.id
            where (:latest is null or (s.created_by,s.created_at) IN ( select created_by,max(created_at) from submission r  group by created_by))
                        and r.thread_id =:topicId
                        and (:status is null or s.status=:status)

Генерално филтрирање на сите topics или проекти

  SELECT * 
                    FROM v_named_threads t 
                    WHERE (:title IS NULL OR t.title ILIKE :title || '%') 
                    AND (CAST(:type as varchar) IS NULL OR t.type ilike :type || '%') 
                    AND (CAST(:content as varchar) IS NULL OR t.content ILIKE :content || '%')
                    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.