如何用SurrealQL一次查询获取三张关联表的全部数据？

问题背景 #

拥有三张业务表directions、services、responsibles，以及两张中间关联表directions_services、services_responsibles。目前可通过两次查询分别获取directions关联的services数据，以及services关联的responsibles数据，但需要通过一次SurrealQL查询获取完整层级关联数据：每个directions包含对应的services数组，每个services又包含对应的responsibles数组，无关联的层级需返回空数组。

现有拆分查询示例 #

-- 查询directions及其关联的services
select *, ->directions_services->services.* as services from directions;

-- 查询services及其关联的responsibles
select *, ->services_responsibles->responsibles.* as responsibles from services;

期望输出格式 #

[
    {
        "id": "directions:first",
        "...": "...",
        "services": [
            {
                "id": "services:serv1",
                "...": "...",
                "responsibles": [
                    { "id": "responsibles:fj0qqith86mnwszvav2s", "..." },
                    { "id": "responsibles:qfotzpu81j4beza0cd84", "..." }
                ]
            },
            { "id": "services:serv2", "...": "...", "responsibles": [] }
        ]
    },
    { "id": "directions:second", "...": "...", "services": [] }
]

错误尝试及问题 #

尝试嵌套子查询后，无关联的directions（如directions:second）也返回了services数据，不符合预期：

SELECT
    *,
(
        SELECT
            *,
            (
                SELECT
                    *
                FROM
                    responsibles
                WHERE
                    <-services_responsibles<-services.id
            ) AS responsibles
        FROM
            services
        WHERE
            <-directions_services<-directions.id
    ) AS services
FROM
    directions;

正确查询语句 #

使用SurrealQL的嵌套关联语法，直接通过关联链绑定层级关系，确保无关联项返回空数组：

SELECT
    *,
    (
        SELECT
            *,
            ->services_responsibles->responsibles.* AS responsibles
        FROM ->directions_services->services
    ) AS services
FROM directions;

逻辑说明 #

• 外层查询从directions表获取所有数据
• 内层子查询通过->directions_services->services直接关联当前direction对应的services，避免了原查询中WHERE条件的匹配错误
• 在services的子查询里，通过->services_responsibles->responsibles.* AS responsibles获取每个service对应的负责人数组，无关联时自动返回空数组

如果需要从services反向关联directions同时获取负责人，可使用：

SELECT
    *,
    <-directions_services<-directions.* AS directions,
    ->services_responsibles->responsibles.* AS responsibles
FROM services;

内容的提问来源于stack exchange，提问作者anon