4

Looking at the example described in Couch DB Joins.

It discusses view collation and how you can have one document for your blog posts, and then each comment is a separate document in CouchDB. So for example, I could have "My Post" and 5 comments associated with "My Post" for a total of 6 documents. In their example, "myslug" is stored both in the post document, and each comment document, so that when I search CouchDB with the key "myslug" it returns all the documents.

Here's the problem/question. Let's say I want to search on the author in the comments and a post that also has a category of "news". How would this work exactly?

So for example:

function(doc) {
  if (doc.type == "post") {
    emit([doc._id, 0], doc);
  } else if (doc.type == "comment") {
    emit([doc.post, 1], doc);
  }
}

That will load my blog post and comments based on this: ?startkey=["myslug"]

However, I want to do this, grab the comments by author bob, and the post that has the category news. For this example, bob has written three comments to the blog post with the category news. It seems as if CouchDB only allows me search on keys that exist in both documents, and not search on a key in one document, and a key in another that are "joined" together with the map function.

In other words, if post and comments are joined by a slug, how do I search on one field in one document and another field in another document that are joined by the id aka. slug?

In SQL it would be something like this:

SELECT * FROM comments JOIN doc.id ON doc.post WHERE author = bob AND category = news
Octavian Helm
  • 39,405
  • 19
  • 98
  • 102
Matt
  • 1,811
  • 1
  • 19
  • 30
  • 1
    This is an interesting question. However I am still unclear exactly what you need. Is it possible to explain how it would work in SQL? Then we can show the Couch way! – JasonSmith Feb 19 '11 at 08:45
  • Not a problem... (forgive me for the syntax)... Select * from comments join doc.id on doc.post where author = bob and category = news . In other words, I'm joining the comments table with the posts table by the slug/id... and then I'm searching by author from comments and by category in posts... so that the result gives me any post with the category news and only the comments by author bob – Matt Feb 19 '11 at 13:23
  • I know exactly what you are looking for. I came across this article http://jamietalbot.com/2010/03/24/using-multiple-start-and-end-keys-for-couchdb-views/ Maybe this is what you need. – Mark Jan 29 '12 at 09:22

2 Answers2

0

There are several ways that you can approximate a SQL join on CouchDB. I've just asked a similar question here: Why is CouchDB's reduce_limit enabled by default? (Is it better to approximate SQL JOINS in MapReduce views or List views?)

  1. You can use MapReduce (not a good option)
  2. You can use lists (This will iterate over a result set before emitting results, meaning you can 'combine' documents in a number of creative ways)
  3. You can also apparently use 'collation', though I haven't figured this out yet (seems like I always get a count and can only use the feature with Reduce - if I'm on the right track)
Zach Smith
  • 8,458
  • 13
  • 59
  • 133
0

I've been investigating couchdb for about a week so I'm hardly qualified to answer your question, but I think I've come to the conclusion it can't be done. View results need to be tied to one and only one document so the view can be updated. You are going to have to denormalize, at least if you don't want to do a grunt search. If anyone's come up with a clever way to do this I'd really like to know.

user235741
  • 1