MaxKB/apps/system_manage/sql/list_mapping_resource.sql at 327a84bbd8d299ed3a96d27b6712443a3ea9a0c0 · 1Panel-dev/MaxKB · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
WITH target_data_cte AS (SELECT 'APPLICATION' as target_type,
                                id,
                                "name",
                                "desc",
                                "user_id",
                                "workspace_id",
                                "icon",
                                "type",
                                "folder_id"
                         FROM application
                         UNION ALL
                         SELECT 'KNOWLEDGE' as target_type,
                                id,
                                "name",
                                "desc",
                                "user_id",
                                "workspace_id",
                                "type"::text as "icon" , "type"::text as "type", "folder_id"
                         FROM knowledge
                         UNION ALL
                         SELECT 'TOOL'      as target_type,
                                id,
                                "name",
                                "desc",
                                "user_id",
                                "workspace_id",
                                "icon",
                                "tool_type" as "type",
                                "folder_id"
                         FROM tool
                         UNION ALL
                         SELECT 'MODEL'      as target_type,
                                id,
                                "name",
                                ''::text as "desc", "user_id",
                                "workspace_id",
                                "provider"   as "icon",
                                "model_type" as "type",
                                ''::text as "folder_id"
                         FROM model)
SELECT rm.*,
       tdc.*,
       u.nick_name as username
FROM resource_mapping rm
         LEFT JOIN target_data_cte tdc
                   ON rm.target_type = tdc.target_type
                       AND rm.target_id::uuid = tdc.id
LEFT JOIN "public"."user" u
ON u.id = tdc.user_id