database - How I can get into from 3 table in one request in surrealQL? - Stack Overflow

admin管理员组

文章数量:1123377

I have 3 tables with data and corresponding intermediate tables:

• directions;
• services;
• responsibles;
• directions_services;
• services_responsibles;

Example:

select *, ->directions_services->services.* as services from directions;
select *, ->services_responsibles->responsibles.* as responsibles from services;

out:

------Query 1------
[
    {
        id: directions:first,
        ...
        services: [
            {
                id: services:serv1,
                ...
            }
        ]
    },
    {
        id: directions:second,
        ...
        services: []
    }
]

------Query 2------
[
    {
        id: services:serv1,
        ...
        responsibles: [
            {
                id: responsibles:fj0qqith86mnwszvav2s,
                ...
            },
            {
                id: responsibles:qfotzpu81j4beza0cd84,
                ...
            }
        ]
    },
    {
        id: services:serv2,
        ...
        responsibles: []
    }
]

How I can get all data in one request?

Example:

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

I tried:

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

And I get this:

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

I don't understand why direction 2-d have a service and responsible. BUT!!! I can use this code and get the correct answer:

select *,
<-directions_services<-directions.* as directions,
->services_responsibles->responsibles.* as responsibles
from services;

I've been working with surrealQL and surrealDB recently, don't judge strictly:)

本文标签： databaseHow I can get into from 3 table in one request in surrealQLStack Overflow