0

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

Najam Awan
  • 1,113
  • 3
  • 13
  • 30

1 Answers1

2

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()
mlucy
  • 5,249
  • 1
  • 17
  • 21
  • I have a users table with Id property can you please tell me how i can join the result of your querty to user table so i can pull email, phone and other info. Also default supply the value if any property is not available on document. like in your code 0 will be injected for credit field if its missing? – Najam Awan Jun 20 '16 at 08:58
  • At the end you can put `.ungroup().merge(function(x) { return {user: r.table('users').get(x('group'))}; })`. (And, yes, `default` provides the default value if a field is missing, or more specifically if any non-existince error occurs while evaluating its left-hand side.) – mlucy Jun 21 '16 at 05:52