SELECT
CASE
WHEN
t.object_type = 1 THEN
'Folder'
WHEN t.object_type = 2 THEN
'File'
WHEN t.object_type = 3 THEN
'Project'
WHEN t.object_type = 14 THEN
'OU'
WHEN t.object_type = 130 THEN
'KnowledgeBase'
WHEN t.object_type = 132 THEN
'KnowledgeFolder'
WHEN t.object_type = 136 THEN
'KnowledgeContainerFolder'
END AS object_type_desc,
CASE
WHEN t.object_type = 1 THEN
( SELECT d.full_path FROM doc_versioneditem d WHERE d.id = t.object_id )
WHEN t.object_type = 2 THEN
( SELECT d.full_path FROM doc_versioneditem d WHERE d.id = t.object_id )
WHEN t.object_type = 3 THEN
( SELECT d.full_path FROM doc_versioneditem d WHERE d.id = t.object_id )
WHEN t.object_type = 14 THEN
( SELECT g.path FROM sitemember_group g WHERE g.id = t.object_id )
WHEN t.object_type = 130 THEN
( SELECT d.full_path FROM doc_versioneditem d WHERE d.id = t.object_id )
WHEN t.object_type = 132 THEN
( SELECT d.full_path FROM doc_versioneditem d WHERE d.id = t.object_id )
WHEN t.object_type = 136 THEN
( SELECT d.full_path FROM doc_versioneditem d WHERE d.id = t.object_id )
END AS 'FULL_PATH'
FROM
doc_filesystemaccessrule t
WHERE
t.const_id = ( SELECT u.id FROM auth_user u WHERE u.username = 'admin' )
AND t.const_type = 2;
SELECT
CASE
WHEN t.object_type IN (1, 2, 3, 130, 132, 136) THEN 'Folder'
WHEN t.object_type = 14 THEN 'OU'
ELSE 'Other'
END AS object_type_desc,
CASE
WHEN t.object_type IN (1, 2, 3, 130, 132, 136) THEN d.full_path
WHEN t.object_type = 14 THEN g.path
ELSE NULL
END AS FULL_PATH
FROM
doc_filesystemaccessrule t
LEFT JOIN doc_versioneditem d ON t.object_id = d.id AND t.object_type IN (1, 2, 3, 130, 132, 136)
LEFT JOIN sitemember_group g ON t.object_id = g.id AND t.object_type = 14
WHERE
t.const_id = (SELECT u.id FROM auth_user u WHERE u.username = 'admin')
AND t.const_type = 2;