如何在没有记录链接的SurrealDB中执行联接

Question

SurrealDB文档指出，通过使用记录链接来替换联接的概念。但是，是否仍然可以基于任意列连接表？

考虑以下表格：

CREATE user:1 SET
    name = 'User1',
    favouriteDay = 'Monday'
;
CREATE user:2 SET
    name = 'User2',
    favouriteDay = 'Tuesday'
;

CREATE motto:1 SET 
    day = 'Monday',
    motto = 'Best day of the week'
;
CREATE motto:2 SET
    day = 'Tuesday',
    motto = 'Second best day of the week'
;

是否可以编写查询以获得以下结果(而不更改底层数据模型)？

"result": [
  {
    "favouriteDay": "Monday",
    "id": "user:1",
    "name": "User1",
    "motto": "Best day of the week"
  },
  {
    "favouriteDay": "Tuesday",
    "id": "user:2",
    "name": "User2",
    "motto": "Second best day of the week"
  }
]

Answer 1

在SurrealDB中确实有一种使用SPLIT连接数据的方法。

连接两个表user和motto的查询如下所示：

SurrealDB

SELECT id, name, favouriteDay, motto.motto as motto 
FROM (
    SELECT *, (SELECT * FROM motto) as motto 
    FROM user 
    SPLIT motto 
) 
WHERE favouriteDay = motto.day;

SQL-equivalent

SELECT id, name, favouriteDay, motto
FROM user JOIN motto ON user.favouriteDay = motto.day

解释

我们首先查询所有用户，并将每个用户的座右铭添加到每个用户。

SELECT *, (SELECT * FROM motto) as motto FROM user

然后，我们可以使用SPLIT将每个user的motto数组中的每个元素展开到一个单独的行。

SELECT *, (SELECT * FROM motto) as motto FROM user SPLIT motto 

最后，我们接受这个查询的结果，过滤favouriteDay和motto.motto匹配的所有行。我们还从motto对象中取出motto字符串，将输出格式化为所需的结果。

SELECT id, name, favouriteDay, motto.motto as motto FROM (
    SELECT *, (SELECT * FROM motto) as motto FROM user SPLIT motto 
) 
WHERE favouriteDay = motto.day;

[
   {
      "favouriteDay":"Monday",
      "id":"user:1",
      "motto":"Best day of the week",
      "name":"User1"
   },
   {
      "favouriteDay":"Tuesday",
      "id":"user:2",
      "motto":"Second best day of the week",
      "name":"User2"
   }
]