RethinkDB:如何对三个表进行递归联接

Question

我正在使用Python Flask开发一个带有JSON API的平台。在某些情况下，我需要连接三个表。如何使用ID数组连接表给了我一些指导，但我需要一个超越它的解决方案

让我们假设一个消息应用程序有三个表。

1. 帐户
2. 对话
3. 消息
4. 消息读取器

账户表片段

{
"id": "account111",
"name": "John Doe",
},

对话表片段

{
"id": "conversation111",
"to": ["account111", "account222", "account333"], // accounts who are participating the conversation
"subject": "RethinkDB",
}

消息表片段

{
"id": "message111",
"text": "I love how RethinkDB does joins.",
"from": "account111", // accounts who is the author of the message
"conversation": "conversation111"
}

Message Readers表片段

{
"id": "messagereader111",
"message": "message111",
"reader": "account111",
}

我的问题是"当我收到id为"account111"的账户文档的获取请求时，获得下面文档的神奇查询是什么？">

{
"id": "account111",
"name": John Doe,
"conversations": [            // 2) Join account table with conversations
{
"id": "conversation111",
"name": "RethinkDB",
"to": [                     // 3) Join conversations table with accounts
{
"id": "account111",
"name": "John Doe",
},
{
"id": "account222",
"name": "Bobby Zoya",
},
{
"id": "account333",
"name": "Maya Bee",
},
]
"messages": [        // 4) Join conversations with messages
{
"id": "message111",
"text": "I love how RethinkDB does joins.",
"from": {        // 5) Join messages with accounts
"id": "account111",
"name": "John Doe",
},
"message_readers": [
{
"name": "John Doe",
"id": "account111",
}
],
},
],
},
],
}

任何指导或建议都非常棒。JavaScript或Python代码会很棒。

Answer 1

我很难理解你想要什么(你有多个id为111的文档)，但我认为这是你正在寻找的查询

Python查询：

r.table("accounts").map(lambda account: 
account.merge({
"conversations": r.table("conversations").filter(lambda conversation: 
conversation["to"].contains(account["id"])).coerce_to("array").map(lambda conversation:
conversation.merge({
"to": conversation["to"].map(lambda account: 
r.table("accounts").get(account)).pluck(["id", "name",]).coerce_to("array"),
"messages": r.table("messages").filter(lambda message:
message["conversation"] == conversation["id"]).coerce_to("array").map(lambda message:
message.merge({
"from": r.table("accounts").get(message["from"]).pluck(["id", "name",]),
"readers": r.table("message_readers").filter(lambda message_reader:
message["id"] == message_reader["message"]).coerce_to("array").order_by(r.desc("created_on")),
})).order_by(r.desc("created_on"))
})).order_by(r.desc("modified_on"))
})).order_by("id").run(db_connection)