Triple JOIN (many to many)

[Fábio Pinheiro]

Hello friends,

I have a question about join in my PouchDB database:

Data:

product:

{ "type": "product", "code": "1", "description": "PRODUCT ONE", "collection": "AAA"},

{ "type": "product", "code": "2", "description": "PRODUCT TWO", "collection": "BBB"}

product_color_image:

{ "type": "product_color_image", "product": "1", "color": "1", ...},

{ "type": "product_color_image", "product": "1", "color": "2", ...},

{ "type": "product_color_image", "product": "1", "color": "3", ...}

Colors:

{ "type": "color", "color": "1", "description": "Blue"},

{ "type": "color", "color": "2", "description": "White"},

{ "type": "color", "color": "3", "description": "Pink"},

{ "type": "color", "color": "4", "description": "Black"},

{ "type": "color", "color": "5", "description": "Red"}

My view:

var docProduct = {

  _id: '_design/product',

  views: {

by_code: {

 map: function (doc) {

if (doc.type == 'product_color_image') {

emit([doc.product, 1]);

} else if (doc.type == 'product') {

emit([doc.code, 0]);

} else if (doc.type == 'color') {

emit([doc.color, 2]);

}

}.toString()

}

  }

};

My query invoke:

db.query('product/by_code', {startkey: ['1'], endkey: ['1',{}], include_docs: true, attachments: false}).then(function (result) {

  console.log(JSON.stringify(result.rows));

});      

Now, I need return a list complete for product (code = 1), with:
- one product object [product.code = 1],
- three product_color_image objects, because they are related to the product 1 [product_color_image.product = 1]
- three colors objects [color.color = product_color_image.color(array)]


But, in my query I set key = 1 and the set this product = 1 in the three emit. Then, I get correctly:
- one product object, 
- three product_color_image objects, because they are related to the product 1

The problem in the colors objects:
only returns the object where the "key" = 1:
{ "type": "color", "color": "1", "description": "Blue"}

How can I set a JOIN in the third part of query, to find the "colors" where product_color_image.color = color.color

 if (doc.type == 'product_color_image') {

emit([doc.product, 1]);

 } else if (doc.type == 'product') {

emit([doc.code, 0]);

 } else if (doc.type == 'color') {

emit([doc.color, 2]); // This is the problem!

 }

thanks for the help!
Fábio

[Nuno Job]

Fabio, not sure if PouchDB took a very different path from CouchDB but normally you tend to denormalize things into documents — so it tends not to have data that looks like that.

--
You received this message because you are subscribed to the Google Groups "PouchDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pouchdb+u...@googlegroups.com.
To post to this group, send email to pou...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pouchdb/b76cd1b1-c351-495d-828f-90978ff8a636%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Giovanni P]

It is impossible to do something like that unless the color document has something indicating the product to which it refers (what would mean you would have one or three colour documents for each product AND that you wouldn't need the product_color_image document).

If possible, this case seems like the solution is denormalizing, getting rid of product_color_image and color documents and just including that information in the product docs. The only other possible solution is to make two queries (this also means you don't need the product_color_image).

To view this discussion on the web visit https://groups.google.com/d/msgid/pouchdb/57061ac77bae512e00000002%40polymail.io.

[Fábio Pinheiro]

[RESOLVED]

Hello,

In my case, the relationship has a lot of information and is updated very often.

Since the goal is to reduce traffic in sync, I decided to create separate documents.

In the documentation, this option is informed as possible (Https://wiki.apache.org/couchdb/EntityRelationship):

"Many to Many: Relationship documents

Another way of implementing many-to-many is by creating a separate document for each relationship.

You would use this method if you modify the key list frequently (i.e. if you get more conflicts than is acceptable), or if the key list is so large that transferring the document is unacceptably slow. Relationship documents enable frequent changes with less chance of conflict; however, you can access neither the contact nor group information in one request. You must re-request those specific documents by ID, keeping in mind that they may change or be deleted in the interim."

The solution:

1 - I had to adjust the model, the keys and the relaciomento name:

{"$doctype": "product", "code": "1", "description": "PRODUCT ONE", "collection": "AAA"},

{"$doctype": "product", "code": "2", "description": "PRODUCT TWO", "collection": "BBB"},

{"$doctype": "color", "_id": "1", "description": "Blue"},

{"$doctype": "color", "_id": "2", "description": "White"},

{"$doctype": "color", "_id": "3", "description": "Pink"},

{"$doctype": "color", "_id": "4", "description": "Black"},

{"$doctype": "color", "_id": "5", "description": "Red"},

{"$doctype": "product-color", "product": "1", "color": "1", "image": "Image1"},

{"$doctype": "product-color", "product": "1", "color": "2", "image": "Image2"},

{"$doctype": "product-color", "product": "1", "color": "3", "image": "Image3"}

2 - The View \o/:

var docProduct = {

_id: '_design/product',

views: {

  by_code: {

  map: function (doc) {

  if (doc.$doctype == 'product-color') {

 emit([doc.product, 1]);                

 emit([doc.product, 2], {_id: doc.color});

  } else if (doc.$doctype == 'product') {

emit([doc.code, 0]);

  }

  }.toString()

  }

}

};

3 - Invoke My function:

return  localDB.query('product/by_code',

                     {startkey: ['1'], endkey: ['1',{}], include_docs: true, attachments: false, group: true}).then(function (result) {

                console.log('*** Car 1: ');

                console.log(JSON.stringify(result.rows));

            })

4 - And the return, with one query:
// 1 - Product

[ { "doc" : { "$doctype" : "product",

        "_id" : "ae0ae08d2652f41e0c284a3a60042d30",

        "_rev" : "1-aeb0453e5197b25a0c45df74a5b2a2c4",

        "code" : "1",

        "collection" : "AAA",

        "description" : "PRODUCT ONE"

      },

    "id" : "ae0ae08d2652f41e0c284a3a60042d30",

    "key" : [ "1",

        0

      ],

    "value" : null

  },
// 3- relationship prodcut-color

  { "doc" : { "$doctype" : "product-color",

        "_id" : "ae0ae08d2652f41e0c284a3a60049263",

        "_rev" : "1-af26e51a7c53023b10489375a798cc26",

        "color" : "1",

        "image" : "Image1",

        "product" : "1"

      },

    "id" : "ae0ae08d2652f41e0c284a3a60049263",

    "key" : [ "1",

        1

      ],

    "value" : null

  },

  { "doc" : { "$doctype" : "product-color",

        "_id" : "ae0ae08d2652f41e0c284a3a60049fcf",

        "_rev" : "1-df6194e9454245ca50f24250994d20f1",

        "color" : "2",

        "image" : "Image2",

        "product" : "1"

      },

    "id" : "ae0ae08d2652f41e0c284a3a60049fcf",

    "key" : [ "1",

        1

      ],

    "value" : null

  },

  { "doc" : { "$doctype" : "product-color",

        "_id" : "ae0ae08d2652f41e0c284a3a6004a280",

        "_rev" : "1-b3e8fd352a3fb9e61dfbf1d62c26ce89",

        "color" : "3",

        "image" : "Image3",

        "product" : "1"

      },

    "id" : "ae0ae08d2652f41e0c284a3a6004a280",

    "key" : [ "1",

        1

      ],

    "value" : null

  },

// 3 - colors

  { "doc" : { "$doctype" : "color",

        "_id" : "1",

        "_rev" : "1-5820c5257ff67676cd0e4f76595240f1",

        "description" : "Blue"

      },

    "id" : "ae0ae08d2652f41e0c284a3a60049263",

    "key" : [ "1",

        2

      ],

    "value" : { "_id" : "1" }

  },

  { "doc" : { "$doctype" : "color",

        "_id" : "2",

        "_rev" : "1-e8b436b26fa97c4c5c3a79f618f8d9aa",

        "description" : "White"

      },

    "id" : "ae0ae08d2652f41e0c284a3a60049fcf",

    "key" : [ "1",

        2

      ],

    "value" : { "_id" : "2" }

  },

  { "doc" : { "$doctype" : "color",

        "_id" : "3",

        "_rev" : "1-b70ff5ae1f110e73227c537ca949202d",

        "description" : "Pink"

      },

    "id" : "ae0ae08d2652f41e0c284a3a6004a280",

    "key" : [ "1",

        2

      ],

    "value" : { "_id" : "3" }

  }

]

Special thanks to Nuno Job and Giovanni Parra for the help!
Fábio