I am looking for a CouchDB equivalent to "SQL joins".
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.
The result could be:
{ "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}] }
I started writing the map function:
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
}
}
}
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.
EDIT: Do not miss JasonSmith's comment to his answer, where he describes how to do this shorter.
Best Answer
Thank you! This is a great example to show off CouchDB 0.11's new features!
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.Here is the plan:
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 ifid
. CouchDB will guarantee uniqueness, but also, the rest of this plan requires_id
in order to fetch documents by ID.If changing the documents to use
_id
is absolutely impossible, you can create a simple view toemit(doc.id, doc)
and then re-insert that into a temporary database. This convertsid
to_id
but adds some complexity.The view emits
{"_id": content_id}
data keyed on[list_id, sort_number]
, to "clump" the lists with their content.Now there is a simple list of
el
documents, in the correct order. You can usestartkey
andendkey
if you want to see only a particular list.To get the
el
content, query withinclude_docs=true
. Through the magic of_id
, theel
documents will load.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.
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.The results match. Of course in production you will need
startkey
andendkey
to specify the list you want.