在SurrealDB中向上遍历关系

Question

问题:如何正确地使用超现实数据库的箭头语法，以便遍历关系树？

我在google上找不到很好的例子，文档也没有详细解释。但对我来说还不够。

我所拥有的：

我在我的数据库中放置了一些有位置的表，并将它们关联起来。这些关系存储在表location中。

我有几张桌子：

• prefecture
• village
• building

“关系结构”如下所示：

• 1等
  • ...
    • 4县
      • 5村
        • 6大楼
        • 7 etc.

选中此选项可查找4、5和6之间的关系：

select * from location where in = village:Akashima or out = village:Akashima and in = prefecture:Chizaki or out = prefecture:Chizaki

结果：

[
  {
    "time": "114.1µs",
    "status": "OK",
    "result": [
      {
        "id": "location:e9thhtqa89mvf9caa0yp",
        "in": "prefecture:Chizaki",
        "out": "village:Akashima"
      },
      {
        "id": "location:j0y4zixgkl7bfohr6cib",
        "in": "village:Akashima",
        "out": "building:WirtshausYukihira"
      },
      {
        "id": "location:lurrfhzvawjmyp06zoh5",
        "in": "village:Akashima",
        "out": "building:HausYukihira"
      },
      {
        "id": "location:zlkd055c75ifk7oi2pqm",
        "in": "country:DE",
        "out": "prefecture:Chizaki"
      }
    ]
  }
]

我试过的事情：

我尝试了这个查询：

SELECT <-village FROM location WHERE out = "building:HausYukihira"

结果就是我现在想要的是：

[
  {
    "time": "242.3µs",
    "status": "OK",
    "result": [
      {
        "<-village": [
          "village:Akashima"
        ]
      }
    ]
  }
]

所以现在我想知道这个村庄位于哪个县，所以我试了一下：

SELECT prefecture<-village FROM location WHERE out = "building:HausYukihira"

和

SELECT <-prefecture<-village FROM location WHERE out = "building:HausYukihira"

结果：

[
  {
    "time": "97.3µs",
    "status": "OK",
    "result": [
      {
        "<-prefecture": {
          "<-village": []
        }
      }
    ]
  }
]

我不知道该把下一支箭放在哪里。

编辑：

用于创建示例数据的SQL

CREATE prefecture:Chizaki
SET
name = 'Chizaki',
type= 'prefecture';

CREATE prefecture:Ijima
SET
name = 'Ijima',
type= 'prefecture';

CREATE prefecture:Shiratama
SET
name = 'Shiratama',
type= 'prefecture';

CREATE village:Kengata
SET
name = 'Kengata',
type= 'village';

CREATE village:Akashima
SET
name = 'Akashima',
type= 'village';

CREATE building:HausYukihira
SET
name = "Haus der Yukihira's",
type= 'building';

CREATE building:WirtshausYukihira
SET
name = "Wirtshaus der Yukihira's",
type= 'building';

CREATE country:KR
SET
name = 'KR',
type= 'country';

CREATE country:DE
SET
name = 'DE',
type= 'country';


RELATE country:DE->location->prefecture:Shiratama;
RELATE country:DE->location->prefecture:Ijima;
RELATE country:DE->location->prefecture:Chizaki;

RELATE prefecture:Chizaki->location->village:Akashima;
RELATE prefecture:Shiratama->location->village:Kengata;

RELATE village:Akashima->location->building:HausYukihira;
RELATE village:Akashima->location->building:WirtshausYukihira;

-- select * from location;
-- select * from location where in = village:Akashima or out = village:Akashima and in = prefecture:Chizaki or out = prefecture:Chizaki;

Answer 1

我建议在building表上开始查询。

<-location<-village

• 到村庄，穿过location表找到村庄

• 前往县城，再次通过location和village横穿。但是现在您还可以从village遍历到location以获得prefecture：location

SELECT 
  *, 
  <-location<-village as village,
  <-location<-village<-location<-prefecture as prefecture
FROM building
WHERE id = "building:HausYukihira"

结果：

[
  {
    "id": "building:HausYukihira",
    "name": "Haus der Yukihira's",
    "prefecture": [
      "prefecture:Chizaki"
    ],
    "type": "building",
    "village": [
      "village:Akashima"
    ]
  }
]

但是，我们也可以按照相同的逻辑查询location表。

SELECT <-village<-location<-prefecture as prefecture
FROM location 
WHERE out = "building:HausYukihira"

结果：

[
  {
    "prefecture": [
      "prefecture:Chizaki"
    ]
  }
]