rethinkdb grouping to calculate balance of user funds

Question

i am using rethinkdb with nodejs. i have a funds table and i am trying to calculate balance of any user by adding all the credit entries minus total debit entries. So far i was able to run following query.

r.db('testDB').table('funds').filter({userId:'63755d1e-e82e-4072-8312-4fcd88f1dfd3'}).group(function(g){ return g('userId') })

this will produce following results.

[
{
"group":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3" ,
"reduction": [
{
"createdAt": Mon Jun 06 2016 14:17:26 GMT+00:00 ,
"createdBy":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3" ,
"credit": 900 ,
"id":  "2afaca8e-6b4f-4ed5-a8ef-7fed3ce5ca67" ,
"userId":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3"
} ,
{
"createdAt": Fri Jun 17 2016 09:02:19 GMT+00:00 ,
"createdBy":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3" ,
"credit": 150 ,
"id":  "c023ea2d-0d28-4f4b-ae6c-1c41c49aca08" ,
"userId":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3"
} ,
{
"createdAt": Fri Jun 17 2016 08:54:56 GMT+00:00 ,
"createdBy":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3" ,
"debit": 50 ,
"id":  "89fd4a56-8722-4e86-8409-d42e4041e38d" ,
"userId":  "63755d1e-e82e-4072-8312-4fcd88f1dfd3"
}
]
}
]

I have tried to use concatMap function and inside that tried using branch to check if its debit or credit but its not working.

this throwing errors
r.db('testDB').table('funds').filter({userId:'63755d1e-e82e-4072-8312-4fcd88f1dfd3'}).group(function(g){
    return g('userId')
}).ungroup().concatMap(function(m){
   //return m('reduction')('credit')
     return r.branch (m('reduction')('credit').gt(0), 'c', 'd')

})

e: Cannot convert STRING to SEQUENCE in:

another approaching using reduce function provide me sum for all the credit entries but i dont know how to sum all debits.

r.db('testDB').table('funds').filter({userId:'63755d1e-e82e-4072-8312-4fcd88f1dfd3'}).group(function(g){
    return g('userId')
}).ungroup().concatMap(function(m){
   return m('reduction')('credit')
    // return r.branch (m('reduction')('credit').gt(0), 'c', 'd')

})
  .reduce(function(left, right){
         return left.add(right);
    })

result is 1050

Answer 1

You probably want something like this:

r.db('testDB').table('funds').group('userId').map(function(row) {
  return row('credit').default(0).sub(row('debit').default(0));
}).sum()