How to Join collections?


#1

How do you join two different collections? Example: In an insurance company, they have enrollments collection and claims collections.

enrollments - list of insurance enrollees
claims - list of insurance claimant from enrollments

If a certain enrollee wanted to claim his insurance, assuming the claim process is approved, the enrollee will be added on the claims collections with additional fields like amount claimed, date claimed etc, on onClick events.

What is the best approach on this?

Thanks,
Marvin


#2

Hi Marvin,

with MongoDB you generally don’t do joins in the SQL sense. What most people do around here is to:

a) de-normalize data, meaning: put everything that you need for accomplishing common tasks like displaying data from the “JOIN”-ed collection into the other collection, duplicating data in the process, but making life that much easier without joins (which are non-natural and somewhat painful in NoSQL land)
b) whenever you then do need the full data you just do another find() with the _id or whatever unique constraint you copied over to the other collection to identify the item to be joined with

(There is at least one package on Atmosphere I believe that is supposed to provide something similar to SQL joins, but I’m not sure any more what it was called and how well that works. But basically you don’t really want to get used to joins anyway when using MongoDB, so never mind that. But you could look for it and try it out, if you wanted to. Or, if what you want is really relational data with real support for JOIN clauses, then you could take a look at the current Postgres integration available for Meteor.)

If that doesn’t fully make it clear to you yet then maybe you’d like to post specific code snippets and it’d be easy to point to specific solutions for you.

Cheers,
Denis


#3

If doing it “by hand” (@seeekr’s solution b) is not possible, you might want to check:



#4

Another great article:
https://www.discovermeteor.com/blog/reactive-joins-in-meteor/


#5

Thanks @seeekr for the insights!

Probably not join collection is not my problem, it’s kind of like duplicating what the documents in a collection then add it on other collection with additional fields.

something like:
enrollments - onClick events - pass a certain fields and add another fields on different collections (claims).

Thanks,
Marvin


#6

You can “simulate” it like this.

Meteor.publish('insurance', function (insuranceId) {
  var insurance = Insurance.findOne(insuranceId);
  return [
    Insurance.find({ _id: insuranceId }),
    Enrollments.find({
      _id: {
        $in: insurance.enrollmentIds
      }
    }),
    Claims.find({
      _id: {
        $in: insurance.claimIds
      }
    })
  ];
});

This subscribes you to that one insurance “agency”, and all the claims and enrollments relevant to that specific insurance.


#7

Thanks so much @corvid!


#8

The closest I got to “simulating” a join was by creating a dummy collection and publishing records to it. Threw the idea out of the window when I realized how slow the thing was, but leaving it as food for thought since it was reactive and it joined data from cursors of the same collection. Could be tweaked to work with different collections as well

// This collection is not to be used as a real collection
// It only exists as a fake collection that we can query for
// published data. Check the publications of the package
// for more info
MyDummyCollection= new Meteor.Collection('MyDummyCollection');
MyDummyCollection.deny({
    'insert': function(){return true;},
    'update': function(){return true;},
    'remove': function(){return true;}
});

if(Meteor.isServer) {
Meteor.publish('JoinedData', function JoinedData() {
    var self = this, published = {}, observers, options = {};

    observers = {
        added: function (sign_id,sign) {
            if(!published[sign_id]) {
                self.added('MyDummyCollection', sign_id, sign);
                published[sign_id] = true;
            }
        },
        changed: function(sign_id, sign){
            self.changed('MyDummyCollection', sign_id, sign);
        },
        removed: function(sign_id, sign){
            if(published[sign_id]) {
                self.removed('AccessibleSigns', sign_id);
                delete published[sign_id];
            }
       }
    };

    var cur1 = MyCollection.find({ /* Your query here */}, options);
    var handle = cur1.observeChanges(_.extend(observers));
    self.onStop(function(){handle.stop();});
 
    
    var cur2 = SignsCollection.find({ /** Query  **/ }, options);
    var handle2 = cur2.observeChanges(_.extend(observers));
    self.onStop(function(){handle2.stop();});
    self.ready();
});
}

Then once this publication is ready, you can query the MyDummyCollection which will contain all the data of all the queries above


#9

Thanks @loupax for the effort!


#10

I think what’s been a big challenge for me is changing my mindset around relational databases. I have not heard a clear argument as to why NOSQL database is a better approach to things. It’s because I’m new to this space as well as Meteor. Can anyone point out any resources that can help me transfer my experience with SQL databases to mongoDB. @seeekr

I want to understand the full thought process behind a NOSQL approach not just using it without a clear understanding as to why and how to work with it.


#11

So after doing further research I found this resource on the Mongo site. in case anyone else is looking for it. Here’s the link SQL to Mongo


#12

I created a sample insurance application generated from @perak meteor-kitchen dataview example for better clarity.

Here’s the github repo: Meteor Insurance Apps

Basically, I have two collections, enrollments and claims.

What I want to do is when you click on the claims icon, next to edit icon, is to pass all the enrollees data to the claims insert form and then when I hit submit button those data from enrollees will now be on claims collection together with the additional fields.

How do you pass the data from one collection to another given the above scenario?

Thanks,
Marvin