CouchDB视图：联接和子查询 [英] CouchDB Views: Joins and Subqueries

问题描述

我将其发布到CouchDB-Users邮件列表中，但我认为我的投放范围会更广。

I posted this to the CouchDB-Users mailing list, but I thought I'd cast my net a little wider.

[由于stackoverflow的新用户而导致的链接被破坏垃圾邮件规则:-(]

[ links destroyed due to stackoverflow's new user spam rules :-( ]

希望你们当中一个聪明人可以帮助
（或者至少有人可以明确地告诉我我要做什么是不可能，我会
必须研究替代方案...我听说MySQL正在接受（-：）。

Hopefully one of you smart people can help (or at least someone can definitively tell me what I'm trying to do is impossible, and I'll have to look into alternatives... I heard MySQL is on the uptake (-: ).

背景信息：
我在这里建立了一个示例数据库：scoates-test.couchone.com/_utils/database.html?follow
/ scoates-test.couchone.com/follow

Background information: I've set up an example database here: scoates-test.couchone.com/_utils/database.html?follow / scoates-test.couchone.com/follow

我有两种类型的文档：type = user和type = asset。

I have two types of documents. type=user and type=asset.

示例用户：scoates-test.couchone.com/_utils/document .html？follow / c988a29740241c7d20fc7974be05f67d

Example user: scoates-test.couchone.com/_utils/document.html?follow/c988a29740241c7d20fc7974be05f67d

示例资产：scoates-test.couchone.com/_utils/document.html?follow/c988a29740241c7d20fc7974be061d62

Example asset: scoates-test.couchone.com/_utils/document.html?follow/c988a29740241c7d20fc7974be061d62

用户可以关注其他用户（类型=用户文档的关注字段） d）。上面的示例
用户（username = bob）是以下 2个用户：

Users can follow other users (the type=user document's "following" field). The above example user (username=bob) is "following" 2 users:

"following": [
   "c988a29740241c7d20fc7974be05ec54", // username=aaron
   "c988a29740241c7d20fc7974be060bb4" // username=dale
]

资产由特定用户拥有。上面的示例资产由c988a29740241c7d20fc7974be061d62（用户名= bob）拥有。

Assets are owned by a specific user. The above example asset is owned by c988a29740241c7d20fc7974be061d62 (username=bob).

希望这是有道理的。

I想要请求属于bob所关注的用户（aaron和dale）的资产，而
我无法投入允许该操作的查看代码。我可以很容易地发出
属于c988a29740241c7d20fc7974be05f67d的所有资产。

I'd like to request assets that belong to users that bob is following (aaron and dale), and I can't put my finger on view code that will allow this. I can easily emit all assets that belong to c988a29740241c7d20fc7974be05f67d.

我可以在两个请求中做到这一点。首先，我向CouchDB索要c988a29740241c7d20fc7974be05f67d，
，然后将以下内容作为键发布到一个视图，该视图返回属于这些键的资产，
，但是您会注意到我的type = asset文档也有一个何时字段，我希望能够通过发出[doc.owner，doc.when]作为键，然后使用startKey / endKey来对
进行排序。
我认为密钥的发布已经结束。

I could do it in two requests. First, I'd ask CouchDB for c988a29740241c7d20fc7974be05f67d, and then POST the following as "keys" to a view that returns assets belonging to those keys, but you'll note that my type=asset documents also have a "when" field, and I want to be able to order by that by emitting [doc.owner, doc.when] as the key, and then using startKey/endKey. So POST of keys is out, I think.

我可以只需在应用程序端加入（查询以下密钥，对每个键分别提出一个
的请求，然后在应用程序端进行排序），但这确实严重破坏了分页（我希望
的每个以下请求都必须限制pageSize），并且得到
很快就失去了控制（如果一个用户关注1000个用户，则每页10,000条记录）。

I could just join on the application side (query the following keys, make one request for each of these keys, then sort on the app side), but this breaks pagination really badly (I'd need to request a limit of pageSize for each following), and that gets out of control very quickly (if a user is following 1000 users, that's 10,000 records per page).

这两种解决方案都不适合我。我想在CouchDB中完成。

Neither of these solutions works for me. I'd like to do it in CouchDB.

我真的很沮丧。请帮忙。

I'm truly stumped. Please help.

S

推荐答案

CouchDB有一个基本规则视图，可以帮助您解决此类问题。

There's a fundamental rule to CouchDB views, which can help you solve this kind of problem.

查询CouchDB视图将返回零，一个或多个键值ID元组，使得键，值和ID为从同一文档计算得出的结果。您可以选择要求提供具有提供的ID的文档（该文档可能是也可能不是用于计算元组的文档）。

Querying a CouchDB view returns zero, one or more key-value-id tuples such that the key, value and id are computed from the same document. You may optionally ask for the document with the provided id (which may or may not be the document that was used to compute the tuple).

如果考虑一下，这是很合逻辑的：每个元组都是 emit（）的结果，因此所有发出的数据只能来自单个文档。

If you think about it, it's fairly logical: every tuple is the consequence of an emit(), so all the emitted data can only come from a single document.

在您的特定情况下，您需要包含元组键：

In your specific case, you need the tuple key to contain:

• 当前用户，因为您只希望该用户可见文档


• 文档的所有者，因为您需要按该值排序。


• 文档的何时，因为您需要按该值排序。

根据基本规则，您需要一个CouchDB文档包含所有这些。您当前的架构（用户文档中的当前用户和文档所有者，文档所有者以及文档文档中的文档所有者）没有将所需的数据汇总在一起，因此您需要对其进行更改。

According to the fundamental rule, you need a single CouchDB document to contain all of these. Your current schema (current user and document owner in the "user" document, document owner and when in the "document" document) does not bring together the required data, so you need to change it.

我的建议是使用具有以下结构的共享文档：

My suggestion would be to use a "sharing" document with the following structure:

{ 
   owner : 'id-of-aaron', 
   followers : [ 'id-of-bob', 'id-of-mike', 'id-of-melissa' ],
   docs : {
     'id-of-doc-1' : '2010-09-08',
     'id-of-doc-2' : '2010-09-07',
     'id-of-doc-3' : '2010-11-27'
   }
}

每当用户关注/取消关注所有者，或者所有者添加或删除文档时，都需要保持此结构更新。从那里，您可以简单地发出：

You need to keep this structure updated whenever an user follows/unfollows the owner, or the owner adds or removes a document. From there, you can simply emit:

for (var docid in doc.docs) 
  for (var i in doc.followers) {
    emit ([doc.followers[i], doc.owner, doc.docs[docid]], { _id : docid });
  }
}

这篇关于CouchDB视图：联接和子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！