如何在SurrealDB中查询单个工作区关联的集合、文件夹与API

背景 #

我有如下SurrealDB Schema：

需求是查询单个工作区下的所有apis、folders和collections，要求每个collection关联对应的folders，每个folder包含其对应的apis，同时消除结果中空的folders数组。

当前查询语句 #

SELECT 
->workspace_to_collection->collection.* AS collection,
(
    SELECT 
        ->collection_to_folder->folder.* 
        AS folders 
    FROM collection 
) AS collection.folder 
FROM workspaces:4yuie4oj7jrrhqvs3m7m;

当前返回结果 #

[
{
    collection: [
        {
            description: 'Collection 2 description',
            folder: [
                {
                    folders: [
                        {
                            description: 'Folder 3 description',
                            id: folder:abu30525i5w0cbz5v2xf,
                            name: 'folder 3',
                            path: '/old',
                            timestamp: d'2024-12-09T04:28:08.053Z'
                        },
                        {
                            description: 'Folder 2 description',
                            id: folder:hj5k62gs0frezfctds6e,
                            name: 'folder 2',
                            path: '/new',
                            timestamp: d'2024-12-09T04:27:58.921Z'
                        },
                        {
                            description: 'Folder 1 description',
                            id: folder:ovmriv3z8xf11p4tiqbd,
                            name: 'folder 1',
                            path: '/create',
                            timestamp: d'2024-12-09T04:27:47.816Z'
                        }
                    ]
                },
                {
                    folders: []
                }
            ],
            id: collection:icloni09dj0pl8knp6qi,
            name: 'Collection 2',
            path: '/dog',
            timestamp: d'2024-12-09T04:24:23.091Z'
        },
        {
            description: 'Collection 1 description',
            folder: [
                {
                    folders: [
                        {
                            description: 'Folder 3 description',
                            id: folder:abu30525i5w0cbz5v2xf,
                            name: 'folder 3',
                            path: '/old',
                            timestamp: d'2024-12-09T04:28:08.053Z'
                        },
                        {
                            description: 'Folder 2 description',
                            id: folder:hj5k62gs0frezfctds6e,
                            name: 'folder 2',
                            path: '/new',
                            timestamp: d'2024-12-09T04:27:58.921Z'
                        },
                        {
                            description: 'Folder 1 description',
                            id: folder:ovmriv3z8xf11p4tiqbd,
                            name: 'folder 1',
                            path: '/create',
                            timestamp: d'2024-12-09T04:27:47.816Z'
                        }
                    ]
                },
                {
                    folders: []
                }
            ],
            id: collection:g3f7ep3th6jbv2b9kcbd,
            name: 'Collection 1',
            path: NULL,
            timestamp: d'2024-12-08T14:25:33.920Z'
        }
    ]
}
]

期望结果结构 #

collection: [
        {
            description: 'Collection 2 description',
            folder: [
                {
                    folders: [
                        {
                            description: 'Folder 3 description',
                            id: folder:abu30525i5w0cbz5v2xf,
                            name: 'folder 3',
                            path: '/old',
                            timestamp: d'2024-12-09T04:28:08.053Z',
                            api: []
                        },
                        {
                            description: 'Folder 2 description',
                            id: folder:hj5k62gs0frezfctds6e,
                            name: 'folder 2',
                            path: '/new',
                            timestamp: d'2024-12-09T04:27:58.921Z',
                            api: [
                                  {method:"post", path:"/"....}
                                 ]
                        },
                        {
                            description: 'Folder 1 description',
                            id: folder:ovmriv3z8xf11p4tiqbd,
                            name: 'folder 1',
                            path: '/create',
                            timestamp: d'2024-12-09T04:27:47.816Z',
                            api: [apis here]
                        }
                    ]
                }
            ],
            id: collection:icloni09dj0pl8knp6qi,
            name: 'Collection 2',
            path: '/dog',
            timestamp: d'2024-12-09T04:24:23.091Z'
        },
        {
            id: collection:icloni09dj0pl8knp6qi,
            name: 'Collection 2',
            path: '/dog',
            timestamp: d'2024-12-09T04:24:23.091Z',
            folder: []
        }
      ]

解决方案 #

要实现需求，需要嵌套查询关联API，同时过滤空数组。可以使用array::compact移除空的folders项，并且在folder的子查询中关联对应的apis：

SELECT 
    ->workspace_to_collection->collection.* AS collection,
    array::compact(
        SELECT 
            {
                folders: array::compact(
                    SELECT 
                        merge(
                            ->collection_to_folder->folder.*,
                            { api: ->folder_to_api->api.* }
                        )
                    FROM collection
                )
            }
        FROM collection
    ) AS collection.folder
FROM workspaces:4yuie4oj7jrrhqvs3m7m;

说明 #

1. 关联API：在folder的查询中，使用merge将folder的字段和对应的api数组合并，确保每个folder包含其关联的apis。
2. 过滤空数组：使用array::compact移除所有空的数组元素，消除结果中多余的空folders项。
3. 嵌套结构优化：通过嵌套子查询确保collection、folder、api的层级关系符合期望。

内容的提问来源于stack exchange，提问作者W.A Rajinda