Reputation: 6725
I use node-linq and I try to understand the GroupBy, but the documentation is so little
for example I have this data :
{"date":"2013/06/26","name":"A","number":1}
{"date":"2013/06/26","name":"A","number":5}
{"date":"2013/06/27","name":"B","number":4}
{"date":"2013/06/27","name":"A","number":4}
and I want write like this query but with LINQ for node js
SELECT date, name, SUM(number)
FROM data
GROUPBY date, name
I try this code but I get Errors
var o = new LINQ(data)
.Select(function(name,d) {return name.statement,d.date;})
.SUM((function(x) {return x.number;)
.GroupBy(function(name,d) {return name.statement,d.date;}
.ToArray()
has somebody any suggestions or just some examples about GroupBy ? Thanks
Upvotes: 1
Views: 7545
Reputation: 13405
While you can see that there's .GroupBy
in the README on github, there is no .GroupBy
when you do npm install node-linq
. So you'll have to install the module with
npm install https://github.com/wearefractal/node-linq/tarball/master
This is your data:
var LINQ = require('node-linq').LINQ;
var data = [ { date: '2013/06/26', name: 'A', number: 1 },
{ date: '2013/06/26', name: 'A', number: 5 },
{ date: '2013/06/27', name: 'B', number: 4 },
{ date: '2013/06/27', name: 'A', number: 4 } ];
First, you want to group them. Unfortunately, you can't group by a hash so you'll have to use a surrogate key:
var o = new LINQ(data).GroupBy(function(row) { return row.date + '~' + row.name; });
console.log(o);
This is the output:
{ '2013/06/26~A':
[ { date: '2013/06/26', name: 'A', number: 1 },
{ date: '2013/06/26', name: 'A', number: 5 } ],
'2013/06/27~B': [ { date: '2013/06/27', name: 'B', number: 4 } ],
'2013/06/27~A': [ { date: '2013/06/27', name: 'A', number: 4 } ] }
Next, you want to sum the numbers, but you can't.
console.log(o.__proto__); // {}
Perhaps you can need to wrap it in LINQ
?
new LINQ(o); // InvalidCastException: Data not Array
Nope.
Let's convert the object to an array by losing the keys. There's no Object.values()
in JavaScript, so we have to do it this way:
o = new LINQ(Object.keys(o).map(function(key) { return o[key] }));
Now we can finally sum all numbers:
o = o.Select(function(rows) { return { date: rows[0].date, name: rows[0].name, sum: new LINQ(rows).Sum(function(row) { return row.number; }) } }).ToArray();
console.log(o);
This is the result:
[ { date: '2013/06/26', name: 'A', sum: 6 },
{ date: '2013/06/27', name: 'B', sum: 4 },
{ date: '2013/06/27', name: 'A', sum: 4 } ]
Not very pretty.
npm install underscore
Here's how to group your data using the underscore
library.
var _ = require('underscore')._;
var data = [ { date: '2013/06/26', name: 'A', number: 1 },
{ date: '2013/06/26', name: 'A', number: 5 },
{ date: '2013/06/27', name: 'B', number: 4 },
{ date: '2013/06/27', name: 'A', number: 4 } ];
var grouper = function(row) { return row.date + '~' + row.name; };
var summer = function(rows) { return rows.reduce(function(sum, row) { return sum + row.number; }, 0); };
var o = _.chain(data)
.groupBy(grouper)
.map(function(rows) { return { date: rows[0].date, name: rows[0].name, sum: summer(rows) }; })
.value();
console.log(o); // same result
Much nicer, I think.
Upvotes: 4
Reputation: 2256
There are some errors in your code there:
For example, instead of:
.Select(function(name,d) {return name.statement,d.date;})
you should write:
.Select(function(item) { return { name: item.name, date: item.date } } )
and instead of:
.GroupBy(function(name,d) {return name.statement,d.date;}
you should write:
.GroupBy(function(item) { return { name: item.name, date: item.date } } )
However, GroupBy is unimplemented for asynchronous link as mentioned in the notes at the top of the ALINQ.coffee (# TODO: GroupBy, ContainsAll)
And ECMAScript prior to 6 does not have big arrow => style lambda notation, so you'd have to use some combinations of map and reduce in order to achieve the same functionality as far as I am aware, which would probably mean enhancing the node-linq library with that capability.
The closest you can get using node-linq right now is probably something like this:
var statement = [{"date":"2013/06/26","name":"A","number":1},
{"date":"2013/06/26","name":"A","number":5},
{"date":"2013/06/27","name":"B","number":4},
{"date":"2013/06/27","name":"A","number":4}]
var o = new LINQ(statement)
.Select(function(item) {
return {
date: item.date,
name: item.name,
sum: new LINQ(statement).Sum(function(item){return item.number;})
};
})
.OrderBy(function(item) {
return [item.name, item.date];
})
.ToArray();
console.log(o);
which gives:
[ { date: '2013/06/26', name: 'A', sum: 14 },
{ date: '2013/06/26', name: 'A', sum: 14 },
{ date: '2013/06/27', name: 'A', sum: 14 },
{ date: '2013/06/27', name: 'B', sum: 14 } ]
In order to fix the sum part, use map and reduce. For example, you could look into lambda-js: https://github.com/dfellis/lambda-js
Upvotes: 3