Напредни извештаи од базата (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)

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

SELECT * 
FROM v_named_threads t 
WHERE (:title IS NULL OR t.title LIKE :title || '%') 
AND (CAST(:type as varchar) IS NULL OR t.type like :type || '%') 
AND (CAST(:content as varchar) IS NULL OR t.content LIKE :content || '%')
AND (CAST(:tags as varchar) IS NULL OR t.tags @> string_to_array(:tags,',')::varchar[])