0

I'm working on a CouchDB use case where I have 3 databases, where I have customers, invoices (which includes an array of invoiceLines) and items. In the invoiceLines array, I currently have this

       "_id": "someId",
      "_rev": "someId",
      "invoiceId": 46,
      "invoiceDate": "11/24/2016",
      "customerId": 85,
      "invoiceLines": [
       {
        "quantity": 10,
        "totalPrice": null,
        "itemId ": 53
       },
       {
        "quantity": 8,
        "totalPrice": null,
        "itemId ": 33
       } 
] } }

Then I want to take the price from my items db and put into the totalPrice value, where it's currently null. Is there an option to take the itemPrice from itemId in the items db? 

 {
  "_id": "someID",
  "_rev": "someRev",
  "itemId": 1,
  "itemName": "gravida",
  "itemPrice": "$39.05"
  }

Hope someone can help and I'm sorry if it's a simple question, but I'm still new to CouchDB and NoSQL.

Community
  • 1
  • 1
jsvi
  • 3
  • 4
  • Why 3 database? If you to do some sort of "joins" with the items and the invoices, you can't do that cross-database. Also, you're asking for `Is there an option to take the item price from itemId in the items db?` ? What do you mean? You want to have an automated way to sum the sub items prices in an invoice or you simply want to update manually the entry? – Alexis Côté Aug 10 '17 at 13:31
  • Well, I'm supposed to do it this way with 3 databases, instead of just adding all to one big database. I mean that I have the prices in items db and I was just guessing that there possibly could be a way I could transfer the price to the invoices db, so I can calculate the quantities into an amount per invoice. – jsvi Aug 10 '17 at 13:59
  • But why don't you just put everything in the same database and split the different document by marking them with a "type" property of "collection" property? This is the common way to do with CouchDB. – Alexis Côté Aug 10 '17 at 14:05
  • Yeah I know, but i'm just testing on small datasets, before launching with big datasets. I thought it would be performance wise better to split the different documents into several db's? But your solution I have looked at and have it created, I just tried to do a split - but that's not possible to do, if I understand it correctly? – jsvi Aug 10 '17 at 14:16
  • If you want to get documents of a specific type, you have to create a view. For example, you could have a view that index all the document by their "type" property. If you want to get the document of type "item", you simply query your view by passing the `key=item` – Alexis Côté Aug 10 '17 at 14:31
  • Yes okay, but I'm having trouble with the database where I stored all 3 types in. I have to get customerId, firstName, number of invoices and the total amount(total price) of the invoices for each customer. Is this possible? – jsvi Aug 10 '17 at 14:37

2 Answers2

0

As for your problem, you can't really use reduce function to get the total price (as you will do with joins in SQL).

What I suggest you to do is the following :

  1. Build a view that emits a complex key like this [invoiceId,{_id:itemId}]
  2. The view that you just build would also emit the actual invoice for the following key : [invoiceId, null]. This way, you get all your informations in one query.
  3. You query your with with your invoiceId and you locally compute the totalprice. This way, if you update the price of a single item, the totalprice will be updated.

Full example:

Invoice template :

{
  "_id": "306860e48b2f6f668c7f409f33000339",
  "_rev": "3-73197f590a5d18b2ee01ebc423cacbb6",
  "type": "invoice",
  "invoiceDate": "11/24/2016",
  "customerId": "customer_1",
  "invoiceLines": [
    {
      "quantity": 10,
      "item": "item_1"
    },
    {
      "quantity": 8,
      "item": "item_2"
    }
  ]
}

Item template

{
  "_id": "item_1",
  "_rev": "2-879798bd718975fe9957a2a699e041d0",
  "type": "item",
  "name": "First item",
  "price": 1
}

View function

function(doc){
    if(doc.type == "invoice"){
        emit([doc._id]);
        if(doc.invoiceLines){
            for(var n in doc.invoiceLines){
                var line = doc.invoiceLines[n];
                emit([doc._id,line.item],{_id:line.item});
            }
        }
    }
}

Now what should you do?

  1. Query the view with those parameters ?key=invoiceId&include_docs=true&group_level=1
  2. Iterates through your data and create a map from the items and assign your invoice to a variable.
  3. Now in your invoice, for each items, set the total price by multiplying the quantity with the item price.

Other solution

Another way to do it (simplier but in two request) 1. Request the invoice document 2. Request the items with the ids from the invoice document. 3. Compute the total price

Also, you could do this:

When you create an invoice, you could link the item id and compute the total price of the item while creating the document. As it's an invoice, we don't care if the price is updated in the future as it's an invoice (correct me if I'm wrong).

Alexis Côté
  • 3,670
  • 2
  • 14
  • 30
  • So your first suggestion is something like this? function (doc) { if (doc.type == "items") { var key = [doc.invoiceId,{_id = doc.itemId}];{ emit(key, null); } } } But that can't be build. With the two request, I can't really understand what you want me to do. – jsvi Aug 11 '17 at 09:12
  • That provides me the items, that have the type = "items", yeah. I query it like this: http://localhost:5984/assignment/_design/view2/_view/typeitems?include_docs=true&conflicts=true - But how do I search to get the total amount for the specific customer, lets say customerId 1 with invoiceId 86, and get some results I can use? – jsvi Aug 11 '17 at 10:49
  • Well you query with the invoiceID: ocalhost:5984/assignment/_design/view2/_view/myviewname?key=enter_the_invoiceid&group_level=1&include_docs=true. This will returns you the items and the invoice. You have to find locally which one is the invoice and from there, you calculate the total prices according to the quantity and the item prices – Alexis Côté Aug 11 '17 at 10:53
  • But @jsvi, does the total price has to be up to date? Let's say I purchase something online, the invoice that I will receive will contains the totalPrice that I paid for my thing. If the price of this items updates, that price in my invoice remains the same. – Alexis Côté Aug 11 '17 at 10:55
  • First: I'm not getting anything except for the items. Second: I want the itemprice to be calculated somehow, so when I get my customer 1 with invoiceid 86, within lies 3 items, it calculates the total price of these 3 items and gives me kinda like (quantity * itemPrice). So it can be written to the "total amount" on the invoice. – jsvi Aug 11 '17 at 11:03
  • Actually(im using Fauxton), but when I have created the map function, it gives me all the items I have in my db. My key and value is showing me null(dont know if it's supposed to). When I run the /view?key=... in my browser it gives me an invalid utf-8 json. – jsvi Aug 11 '17 at 11:18
  • See my updated version. Yes, in fauxton you must encode everything in JSON – Alexis Côté Aug 11 '17 at 11:19
  • I have created an "answer". – jsvi Aug 11 '17 at 12:18
0

Well okay. You're invoice template has 

"invoiceLines": [
    {
      "quantity": 10,
      "item": "item_1"
    },
    {
      "quantity": 8,
      "item": "item_2"
    }

Where mine have this: 

"invoiceLines": [
   {
    "quantity": 6,
    "totalPrice": null,
    "itemId ": 43
   },
   {
    "quantity": 4,
    "totalPrice": null,
    "itemId ": 58
   },

And you're giving your _id in items another value as well? Mine is looking like this 

 "_id": "a884e9095e6ce843c16d85a6e1163215",
  "_rev": "2-5ff2a928b68b8d23001b012f0e272626",
  "itemId": 14,
  "type": "items",
  "itemName": "dictum",
  "itemPrice": "$33.65"

So you're solution is fine, but then I will have to change my ItemId property to items, if I understand it correctly? Sorry if this is giving you a headache, but I'm really new to both javascript and couch. Thanks for helping.

jsvi
  • 3
  • 4
  • If we take an item, why would you have a _id and a itemId? What's the difference between both? If you still want to keep two ids, then my solution can be use. Also, I changed the invoiceLines content a bit. You can use itemId as long as the value is a document _id. You can also add the totalPrice but it will be null (should only be computed locally) – Alexis Côté Aug 11 '17 at 17:24