Getting sum of values grouped by predicate

Users
1

I’m starting (veery slowly) to wrap my head around variables in dgraph queries, and have a specific problem. Imagine the following data:

{
"set": [
    {
        "isPaymentType": true;
        uid: "_:cash";
    },
    {
        "isPaymentType": true;
        uid: "_:card";
    },
    {
      "isBill": true,
      "items": [
      	{ "amount": 10 },
    	{ "amount": 2 }
      ],
      "payments": [
         {
             "amount": 20,
             "paymentType": {
                 "uid": "_:cash";
             }
         }
      ]
    },
   {
      "isBill": true,
      "items": [
        { "amount": 5 },
        { "amount": 3 }
      ]
      "payments": [
         {
             "amount": 5,
             "paymentType": {
                 "uid": "_:card";
             }
         },
         {
             "amount": 3,
             "paymentType": {
                 "uid": "_:cash";
             }
         },
      ]
    }
  ]
}

As you can see each bill could have multiple items and multiple payments of different “paymentTypes” (in this case cash and card). My question is how would I could the total values (from “amount”) of the “payments” in each bill grouped by the payment type? Looking at “@groupby” I see I can only really get count(uid) or other aggregators from that.

It’s very clear to me that I’m just not thinking in a properly dgraph way yet. I’m sure this problem is pretty trivial, but some pointers towards how I need to think about this would really help

2

I’m still analysing your case but why not this?

  {
      "isBill": true,
      "items": [
        { "amount": 5,
          "paymentType": {"uid": "_:card"},
          "waitingpayment": true },
        { "amount": 3,
          "paymentType": {"uid": "_:cash"},
          "waitingpayment": false }
  }
3

Thanks for replying! But clearly my question was badly formed. What I’m looking for is a query that would give me the total value of payments grouped by payment type. In the case I put in the original question, I would get:

total: 20
cashTotal: 23 (8 given as change)
cardTotal: 5

Does that make sense?

4

Actually gave only one suggestion.

This is total of items? if so, I think can’t be done by this query. You have to do two blocks.

Mutation

{
   "set":[
      {
         "isPaymentType":true,
         "Payment.Type": "Cash",
         "uid":"_:cash"
      },
      {
         "isPaymentType":true,
        "Payment.Type": "Card",
         "uid":"_:card"
      },
      {
         "isBill":true,
         "items":[
            {
               "amount":10
            },
            {
               "amount":2
            }
         ],
         "payments":[
            {
               "amount":20,
               "paymentType":{
                  "uid":"_:cash"
               }
            }
         ]
      },
      {
         "isBill":true,
         "items":[
            {
               "amount":5
            },
            {
               "amount":3
            }
         ],
         "payments":[
            {
               "amount":5,
               "paymentType":{
                  "uid":"_:card"
               }
            },
            {
               "amount":3,
               "paymentType":{
                  "uid":"_:cash"
               }
            }
         ]
      }
   ]
}

Query

{
  var(func:has(payments)) {
   payments @groupby(paymentType){ 
    A as count(uid)
  }
}
  
   q(func: uid(A)) @normalize
  {
    Payment.Type : Payment.Type
    isPaymentType : isPaymentType
    ~paymentType {
  		AM as amount
 		 }
    Total : sum(val(AM))
  }
}

Result

{
  "data": {
    "q": [
      {
        "Payment.Type": "Cash",
        "isPaymentType": true,
        "Total": 23
      },
      {
        "Payment.Type": "Card",
        "isPaymentType": true,
        "Total": 5
      }
    ]
  }
}
1 Like
5

Thank you so much Michel!

I’m so backwards when it comes to dgraph. It hadn’t occurred to me to use the reverse edge on payment type to get the amount.

Now I just need to figure out how to do this on a specific subset of bills (based on datetime). I’m guessing a combination of var queries (one for the bills in question, to get the uid of the payments associated with those bills, then use that collection of uids as a filter for the “~paymentType” edge)

1 Like
6

BTW, You don’t need even @groupby

{
   q(func:has(isPaymentType)) @normalize
  {
    uid
    Payment.Type : Payment.Type
    isPaymentType : isPaymentType
    ~paymentType {
    AM as amount
    }
    Total : sum(val(AM))
  }
}
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.