I'm trying to model a fairly simple relationship in CouchDB and I'm having trouble determining the best way to accomplish this. I'd like users to be able to create lists of video game objects. I have the video game documents stored in the DB already with "type":"game"
. I'd like to be able to query a list object's ID (via a view) and get back the list's metadata (title, creation date, etc.) and portions of the game document (such as title and release date). Furthermore, I'd like to be able to add/removes games to/from lists without downloading the entire list document and posting it back (so this means I can't simply store the game's information in the list document) as I'd eventually like to support multiple users contributing to the same list, and I don't want to introduce conflicts.
After reading the CouchDB wiki on EntityRelationships, I've determined that setting up relationship documents might be the best solution.
{
"_id": "2600emu",
"type": "game"
}
{
"_id": 123,
"title": "Emulators",
"user_id": "dstaley",
"type": "list"
}
{
"_id": "98765456789876543",
"type": "relationship",
"list_id": 123,
"game_id": "2600emu"
}
But, from what I understand, this wouldn't allow me to get the list's metadata and the game's metadata in one request. Any advice?
Great question. You identify a few very important reasons that using a "normalized" data model (different document types with links) is an optimal model:
You're also right that the solution here is a 'map-side-join' (to borrow from the hadoop community). Basically you want to use different rows in the map output to represent different pieces of information. You can then use a range query (startkey/endkey) to query just the portion of the map result that you need, and, voila, your materialized view of the 'join' table. However, the one piece of the puzzle you didn't find in the documentation is this:
3.2.3. Joins With Views
3.2.3.1. Linked Documents
If your map function emits an object value which has
{'_id': XXX}
and you query view withinclude_docs=true
parameter, then CouchDB will fetch the document with idXXX
rather than the document which was processed to emit the key/value pair.
Says it all. That's how you dereference the pointer to the linked document that you have stored by a foreign key. You then combine that with the use of compound keys (keys that are JS arrays) and the view collation rules.
So that your view rows we be sorted like:
["list_1"], null
["list_1", "game"], {"_id":"game_1234"}
["list_1", "game"], {"_id":"game_5678"}
["list_2"], null
["list_2","game"], {"_id":"game1234"}
["list_3"], null
...
Putting this together with your existing data model, here is some (untested) pseudocode that should do the trick:
function(doc) {
if (doc.type=="list") {
//this is the one in the one-to-many
emit( [doc._id]),);
}
else if (doc.type=="relationship") {
//this is the many in the one-to-many
//doc.list_id is our foreign key to the list. We use that as the key
//doc.game_id is the foreign key to the game. We use that as the value
emit( [doc.list_id,'game'], {'_id': doc.game_id});
}
}
Finally, you would query that with a startkey/endkey so that you get all rows that begin with the list_id that you're interested in. It would look something like:
curl -g 'https://usr:[email protected]/db/_design/design_doc_name/_view/view_name?startkey=["123"]&endkey=["123",{}]&include_docs=true'
The -g
option tells curl not to glob, which means you don't have to dereference your square brackets, etc, and the include_docs=true
option will follow the pointer to the foreign key that you specified with game_id
in the relationship
document.
Analysis:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With