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

问题描述

我正在寻找一个等同于"SQL joins"的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.

当然，您也可以编写其他映射函数.但是文档的结构(每个条目一个definig元素文档和一个条目文档)无法更改.

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新的 功能！

Thank you! This is a great example to show off CouchDB 0.11's new features!

您必须使用提取相关数据功能来引用文档 (可选)，为了更方便地使用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中解析信息.下一个可选 步骤只需将其重新格式化即可满足您的需求.

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屋！