How to mimic SQL group, count(*), join, union and derived (in reactive way)


I do not know if this the right place to ask, but I do not know any other place I could go. I have a very big problem. I have used 100h to build up a meteor application for managing hockey tournaments that me and my friends are arranging. My application is 100% reactive with nice feature such as schedule with live clock, standings with all calculation, game rosters etc. I have done everything else, but my last thing would be to add statistics for the players, which I have not been able do in reactive way :frowning: Our first tournament is in 2 days so I am really hopeless, because I am running out of time.

I have tried aggregate $out on server-side to write collection of players and their goals when match is changed to closed and bouncing this collection in helper, but this does not work in reactive way for some reason and helper wont get the updated data everytime even pushing the refresh button on browser.

I am hopeless, this was quite easy thing to do with my previous system which was built on LAMP, I just used Groups, Count, Join, Union and Derived.


“event_type” : “Goal”,
“team_name_event” : “Pittsburg”,
“player_name_event” : “Crosby Sidney”

“event_type” : “Goal”,
“team_name_event” : “Pittsburg”,
“player_name_event” : “Letang Kris”,
“player_name_event_second” : “Crosby Sidney”
“event_type” : “Goal”,
“team_name_event” : “Minnesota”,
“player_name_event” : “Grandlund Mikael”,
“player_name_event_second” : “Koivu Mikko”,
“player_name_event_third” : “Haula Erik”


player team G A P Crosby Sidney Pittsburg 1 1 2 Letang Kris Pittsburg 1 0 1 Granlund Mikael Minnesota 1 0 1 Koivu Mikko Minnesota 0 1 1 Haula Erik Minnesota 0 1 1

1 Like

Server-side code is not reactive by definition. There are several ways around it. The way I solved it is by using excellent job-collection package, and creating a job to calculate metrics every time something changes. For example, I add a job to calculate “total items” on every insert and remove on Items collection, and so on. These jobs output their results to a separate Metrics collection, to which I can subscribe on the client and have near real-time data displayed.

@M4v3R Do the Meteor methods and publish/subcribe work when insecure is installed? I still have it. Job-collection looks promising, but I am not sure if I can accomplish to learn job-collection within one day. I am currently checking out if underscore can help. I think that underscore.js could do it on client-side as I will have no more than 70 objects (goals) to processed per tournament.

They should work just fine. All insecure does is executing Collection.allow on all your collections so you can modify data on the client without restrictions.

I managed to learn and implement job-collection for this purpose within few hours, so I think that’s doable :).

I think you have a bit more skill than me :smile:

@M4v3R What do you think of processing this with Underscore.js? I think it would be okay to use Underscore.js to process about 70 objects on client-side? I just got into Underscore and I cannot figure out how to do it :frowning:

70 objects should not be a problem for the client-side. What do “A” and “P” column mean in your desired output data? Also, how does player_name_event_second field get mapped to these?

=> Player who makes the goal (G)

=> Player who gives the pass (A)

=> Player who gives the second pass (A)

P = G + A

And why in the second record player_name_event is player’s name (Letang Kris), but in the third record it’s team’s name (Minnesota)?

I mistyped first, sorry. Now edited

I loved this part the most, good luck guys! :slightly_smiling:

@brajt Thx. Only problem is that my gf will destroy me anytime soon as I’ve been on computer doing this application since last friday :slightly_smiling: Ultimate brainfog is a mild description under this pressure :–D

1 Like

##this is where I am atm
memo = Memo.find({event_type: “Maali”, tournament_short_url: tournament_short_url}).fetch();

uniquearray = _.countBy(memo, “player_name_event”);

var goals =, function(value, key) {
return {player_name_event: (key), count:value};

return goals;

what about 40 dollars for solving this? :smiley:

1 Like

I have this (which is pretty horrible, due to the data structure) which works for the sample data above. It makes an events object which looks like this:

  "Pittsburg": {
    "Crosby Sidney": [1,1,2],
    "Letang Kris": [1,0,1]
  "Minnesota": {
    "Grandlund Mikael": [1,0,1],
    "Koivu Mikko": [0,1,1],
    "Haula Erik": [0,1,1]

The code is this (includes the sample data):

const data = [{
    "event_type": "Goal",
    "team_name_event": "Pittsburg",
    "player_name_event": "Crosby Sidney"
}, {
    "event_type": "Goal",
    "team_name_event": "Pittsburg",
    "player_name_event": "Letang Kris",
    "player_name_event_second": "Crosby Sidney"
}, {
    "event_type": "Goal",
    "team_name_event": "Minnesota",
    "player_name_event": "Grandlund Mikael",
    "player_name_event_second": "Koivu Mikko",
    "player_name_event_third": "Haula Erik"

const events = {};
data.forEach((doc) => {
    if (!events[doc.team_name_event]) {
        events[doc.team_name_event] = {};
    if (!events[doc.team_name_event][doc.player_name_event]) {
        events[doc.team_name_event][doc.player_name_event] = [0, 0, 0];
    if (doc.player_name_event_second) {
        if (!events[doc.team_name_event][doc.player_name_event_second]) {
            events[doc.team_name_event][doc.player_name_event_second] = [0, 0, 0];
        if (doc.player_name_event_third) {
            if (!events[doc.team_name_event][doc.player_name_event_third]) {
                events[doc.team_name_event][doc.player_name_event_third] = [0, 0, 0];

(I warned you it was horrible!)

1 Like

I agree it looks horrible, but hey, it works! :slightly_smiling:

Does it? Indeed, it’s quite massive!

Not so far away with underscore

memo = Memo.find({event_type: “Goals”}).fetch();
players = Players.find({}).fetch();

goalsArray = _.countBy(memo, "player_name_event");    
assistsArray = _.countBy(memo, "player_name_event_first");
assistsArray = _.countBy(memo, "player_name_event_second");

var assists =, function(value, key) {
return {player_name_event: (key), assists:value};

var goals =, function(value, key) {
return {player_name_event: (key), goals:value};

Ah well, if I’d known about the Goals and Players collections…