进行一对多“JOIN"的最佳方式在 CouchDB 中 [英] Best way to do one-to-many "JOIN" in CouchDB

问题描述

我正在寻找相当于SQL 连接"的 CouchDB.

I am looking for a CouchDB equivalent to "SQL joins".

在我的示例中，CouchDB 文档是列表元素:

In my example there are CouchDB documents that are list elements:

{ "type" : "el", "id" : "1", "content" : "first" } 
{ "type" : "el", "id" : "2", "content" : "second" } 
{ "type" : "el", "id" : "3", "content" : "third" } 

有一个文档定义了列表:

There is one document that defines the list:

{ "type" : "list", "elements" : ["2","1"] , "id" : "abc123" }

如您所见，第三个元素已被删除，它不再是列表的一部分.所以它不能是结果的一部分.现在我想要一个返回内容元素的视图，包括正确的顺序.

As you can see the third element was deleted, it is no longer part of the list. So it must not be part of the result. Now I want a view that returns the content elements including the right order.

结果可能是:

{ "content" : ["second", "first"] }

在这种情况下，元素的顺序已经是应该的了.另一个可能的结果:

In this case the order of the elements is already as it should be. Another possible result:

{ "content" : [{"content" : "first", "order" : 2},{"content" : "second", "order" : 1}] }

我开始写地图功能:

map = function (doc) {
  if (doc.type === 'el') {
    emit(doc.id, {"content" : doc.content}); //emit the id and the content
    exit;
  }
  if (doc.type === 'list') {
    for ( var i=0, l=doc.elements.length; i<l; ++i ){
      emit(doc.elements[i], { "order" : i }); //emit the id and the order
    }
  }
}

这是我所能得到的.你能纠正我的错误并写一个reduce函数吗?请记住，第三个文档不能是结果的一部分.

This is as far as I can get. Can you correct my mistakes and write a reduce function? Remember that the third document must not be part of the result.

当然你也可以写一个不同的地图函数.但是文档的结构(一个定义元素文档和每个条目的条目文档)不能更改.

Of course you can write a different map function also. But the structure of the documents (one definig element document and an entry document for each entry) cannot be changed.

不要错过 JasonSmith 对他的回答的评论，他在其中描述了如何更短地执行此操作.

Do not miss JasonSmith's comment to his answer, where he describes how to do this shorter.

推荐答案

谢谢！这是展示 CouchDB 0.11 的好例子新的功能！

您必须使用 fetch-related-data 功能来引用文档 (可选)为了更方便的 JSON，使用 _list 函数清理结果.请参阅 Couchio 关于JOIN"的文章 了解详情.

You must use the fetch-related-data feature to reference documents in the view. Optionally, for more convenient JSON, use a _list function to clean up the results. See Couchio's writeup on "JOIN"s for details.

计划如下:

1. 首先，您的 el 文档具有唯一性约束.如果两个他们有 id=2，这是个问题.有必要使用_id 字段而不是 id.CouchDB 将保证唯一性，而且，该计划的其余部分需要 _id 以便按 ID 获取文档.

1. Firstly, you have a uniqueness contstraint on your el documents. If two of them have id=2, that's a problem. It is necessary to use the _id field instead if id. CouchDB will guarantee uniqueness, but also, the rest of this plan requires _id in order to fetch documents by ID.

{ "type" : "el", "_id" : "1", "content" : "first" } 
{ "type" : "el", "_id" : "2", "content" : "second" } 
{ "type" : "el", "_id" : "3", "content" : "third" } 

如果更改文档以使用 _id 绝对不可能，您可以为 emit(doc.id, doc) 创建一个简单视图，然后将其重新插入到临时数据库.这会将 id 转换为 _id，但会增加一些复杂性.

If changing the documents to use _id is absolutely impossible, you can create a simple view to emit(doc.id, doc) and then re-insert that into a temporary database. This converts id to _id but adds some complexity.

视图发出 {"_id": content_id} 键控数据[list_id, sort_number]，将列表与其内容组合"起来.

The view emits {"_id": content_id} data keyed on [list_id, sort_number], to "clump" the lists with their content.

function(doc) {
  if(doc.type == 'list') {
    for (var i in doc.elements) {
      // Link to the el document's id.
      var id = doc.elements[i];
      emit([doc.id, i], {'_id': id});
    }
  }
}

现在有一个简单的 el 文档列表，按正确的顺序排列.你可以如果您只想查看特定列表，请使用 startkey 和 endkey.

Now there is a simple list of el documents, in the correct order. You can use startkey and endkey if you want to see only a particular list.

curl localhost:5984/x/_design/myapp/_view/els
{"total_rows":2,"offset":0,"rows":[
{"id":"036f3614aeee05344cdfb66fa1002db6","key":["abc123","0"],"value":{"_id":"2"}},
{"id":"036f3614aeee05344cdfb66fa1002db6","key":["abc123","1"],"value":{"_id":"1"}}
]}

要获取 el 内容，请使用 include_docs=true 进行查询.通过魔法_id，el 文件将被加载.

To get the el content, query with include_docs=true. Through the magic of _id, the el documents will load.

curl localhost:5984/x/_design/myapp/_view/els?include_docs=true
{"total_rows":2,"offset":0,"rows":[
{"id":"036f3614aeee05344cdfb66fa1002db6","key":["abc123","0"],"value":{"_id":"2"},"doc":{"_id":"2","_rev":"1-4530dc6946d78f1e97f56568de5a85d9","type":"el","content":"second"}},
{"id":"036f3614aeee05344cdfb66fa1002db6","key":["abc123","1"],"value":{"_id":"1"},"doc":{"_id":"1","_rev":"1-852badd683f22ad4705ed9fcdea5b814","type":"el","content":"first"}}
]}

注意，这已经是您需要的所有信息了.如果您的客户是灵活，您可以从这个 JSON 中解析出信息.下一个可选step 只需将其重新格式化以匹配您的需要.

Notice, this is already all the information you need. If your client is flexible, you can parse the information out of this JSON. The next optional step simply reformats it to match what you need.

使用 _list 函数，它只是重新格式化视图输出.人们使用它们来输出 XML 或 HTML，但是我们将制作JSON更方便.

Use a _list function, which simply reformats the view output. People use them to output XML or HTML however we will make the JSON more convenient.

function(head, req) {
  var headers = {'Content-Type': 'application/json'};
  var result;
  if(req.query.include_docs != 'true') {
    start({'code': 400, headers: headers});
    result = {'error': 'I require include_docs=true'};
  } else {
    start({'headers': headers});
    result = {'content': []};
    while(row = getRow()) {
      result.content.push(row.doc.content);
    }
  }
  send(JSON.stringify(result));
}

结果匹配.当然在生产中你需要 startkey 和 endkey 来指定你想要的列表.

The results match. Of course in production you will need startkey and endkey to specify the list you want.

curl -g 'localhost:5984/x/_design/myapp/_list/pretty/els?include_docs=true&startkey=["abc123",""]&endkey=["abc123",{}]'
{"content":["second","first"]}

这篇关于进行一对多“JOIN"的最佳方式在 CouchDB 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！