How to do table join of mongodb (aggregate $lookup) using meteor

[Mobile version(QRCode)] Total views:65,407 / Applauds for blog:1
Welcome to my page. I am an adminisrator of this site.
If you are this db's user, please contact with me by private mail. If not, please contact with me by email or twitter or facebook
Access record[Graph / PV Info.(Past 1 day / Past 1 week) / Access from outside (Yesterday / Past 1 week) / Vistors's list]
ProfilePmail(Mail)
Inbox   /Send   /Sent
Reviews(List   /Limit)
Poll   /Agree:Got   /Sent
Fan
Works/Music
Blog
[Write]
Links
My Play List
<=Newer article MongoDb's naming convention
=>Older article Use multiple desktop windows in Mac

1.
2017/11/18 (Updated 2017/11/19) "Meteor > How to do table join of mongodb (aggregate $lookup) using meteor"
[Show only this article / Modify / Delete / Send trackback / Add to the shared category]

1. Purpose of the documents
2. How to do "table join" using mongodb's shell
3. How to do "table join" in Meteor
    1. The way of using Association helpers in official document
    2. The way of using MongoDB's aggregate $lookup function for table join
        1. Preparation in meteor PJ folder
        2. How to implement
          1. Example of publications.js
          2. Example of container
          3. Example of views in JSX

1. Purpose of the documents

MongoDB was not assumed as DB whose usage will require join of tables with serious consideration, so it was so for Meteor, too.
But it gives us the difficulty for data integrity.
As consequence of the necessity, MongoDB evolved with more support for "table join" but we still cannot find enough info for "table join" for meteor.
So I researched and summarized the way of implementing table join of mongodb using meteor.
2. How to do "table join" using mongodb's shell

Example of how to "join" ContriesToLists table and Lists table by ContriesToLists.listId and Lists._id
You can refer to https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#pipe._S_lookup
for creating the query.

db.CountriesToLists.aggregate([
    {
      $lookup:
        {
          from: "Lists",
          localField: "listId",
          foreignField: "_id",
          as: "listInfo"
        }
   },
  { $match: { $and: [{"countryCode":"JP"}] } },
  { $match: { $or: [{ "listInfo.isPrivate": false }, { "userId" : "QGANTWSsNAoR8dsXC"}] } },
  { "$sort": { "updatedAt": -1 } },
  { "$limit": 100 },
]);
So if we can use same expression for getting the result even in meteor, it means that we can do table join in meteor making use of mongodb's function fullly. 
3. How to do "table join" in Meteor


    1. The way of using Association helpers in official document

https://guide.meteor.com/collections.html#collection-helpers

CountriesToLists.helpers({
  lists() {
    return Lists.find({_id: this.listId} });
  }
});

const countriesToLists = CountriesToLists.find({});

And filter again using the value of countriesToLists.lists but not so ideal way because we cannot solve the necessity just by 1 mongodb queries and have to do many things to get the wanted result.
    2. The way of using MongoDB's aggregate $lookup function for table join


      1. Preparation in meteor PJ folder

Add meteor module to your meteor project.
meteor add jcbernack:reactive-aggregate

      2. How to implement

The core of implementation is how to implement the code for publication but I show you the actual related code for this.
Please extract the concept seeing examples.
        1. Example of publications.js

This is the file from one of my project as an example.

imports/api/countriesToLists/server/publications.js
import { Meteor } from 'meteor/meteor';
import { CountriesToLists } from '../countriesToLists.js';


Meteor.publish('citiesToLists.FindByCityName', function (params) {
    let limit = 100;

    check(params, {countryCode: String, cityName: String, stateName: String});
    let cityName = params.cityName;
    let countryCode = params.countryCode;
    let stateName = params.stateName;
    let andCond = [{"countryCode": countryCode}, {"stateName": stateName}, {"cityName": cityName}];
    let orCond = [{"listInfo.0.isPrivate": false}];
    let userId = Meteor.userId();
    if (userId) {
        orCond.push({ "listInfo.0.userId": userId});
    }

    return ReactiveAggregate(this, CitiesToLists, [
        {
          $lookup:
            {
              from: "Lists",
              localField: "listId",
              foreignField: "_id",
              as: "listInfo"
            }
        },
        { $match: { $and: andCond } },
        { $match: { $or: orCond } },
        { $sort: { updatedAt: -1 } },
        { $limit: limit },
    ]);
});

        2. Example of container

This is the file from one of my project as an example.

imports/ui/containers/CountryPageContainer.jsx
import { Meteor } from 'meteor/meteor';
import { createContainer } from 'meteor/react-meteor-data';
import { Countries } from '../../api/countries/countries.js';
import { States } from '../../api/states/states.js';
import { CountriesToLists } from '../../api/countriesToLists/countriesToLists.js';
import CountryPage from '../pages/CountryPage.jsx';

const CountryPageContainer = createContainer(({ params: { countryCode } }) => {
  const countriesToListHandle = Meteor.subscribe('countriesToList.FindByCountryCode', { countryCode: countryCode });
  const countriesHandle = Meteor.subscribe('countries.FindByCountryCode', { countryCode: countryCode });
  const statesHandle = Meteor.subscribe('states.FindByCountryCode', { countryCode: countryCode });

  const loading = !countriesToListHandle.ready() || !countriesHandle.ready() || !statesHandle.ready();

  const countriesToLists = CountriesToLists.find({countryCode: countryCode}).fetch();
  const states = States.find({countryCode: countryCode}, {sort: {stateName: 1}}).fetch();
  const country = Countries.findOne({countryCode: countryCode});
  const countriesToListExist = !loading && !!countriesToLists;
  return {
    country,
    countryCode,
    loading,
    countriesToLists,
    countriesToListExist,
    states
  };
}, CountryPage);

export default CountryPageContainer;

        3. Example of views in JSX

This is the file from one of my project as an example.

imports/ui/components/ListList.jsx
import React from 'react';
import PropTypes from 'prop-types';
import { Link } from 'react-router';
import BaseComponent from './BaseComponent.jsx';
import i18n from 'meteor/universe:i18n';
import isDefined from '@hikarine3/is-defined';
import Media from 'react-bootstrap/lib/Media';
import Paper from 'material-ui/Paper';

export default class ListList extends BaseComponent {
  constructor(props) {
    super(props);
  }

  render() {
    console.log("in ListsList render()");
    const { lists, loading, user } = this.props;

    if (loading) {
      return {};
    }
    else {
      return (
        <Paper className="paper">
        <ul>
          {isDefined(lists, "map") ?
          lists.map(list => 
          {
            let listId = list.listId || list._id;

// There are 2 type of data format for this flow, so I am using if condition but you can get the joined data by $MainTable.$joinedAsName[$arrayIndex].$columnName
            let listTitle = "";
            if ( isDefined(list.listInfo) && list.listInfo[0].name) {
              listTitle = list.listInfo[0].name;
            }

            else{
              listTitle = list.listName || list.name;
            }
            let isPrivate = false;
            if ( isDefined(list.listInfo) && list.listInfo[0].isPrivate) {
              isPrivate = list.listInfo[0].isPrivate;
            }
            else if(isDefined(list.isPrivate)){
              isPrivate = list.isPrivate;
            }

            let thumbnailImg = "";
            if ( isDefined(list.imgUrl) ) {
              thumbnailImg = convertToThumbnail(list.imgUrl);
            }
          return (
        <li key={list._id}>
        <Link
to={`/lists/${listId}/`}
key={listId}
className="list-todo"
activeClassName="active"
>
    <Media>
      <Media.Left align="top">
        {thumbnailImg
        ?
        Image
        :null
        }
      </Media.Left>
      <Media.Body>
            {isPrivate == true ?
              <i className="fa fa-lock">
              :
              null
            }
            {!isPrivate && Meteor.userId() && list.userId && Meteor.userId() === list.userId
            ? <i className="fa fa-edit"> : null }
            {listTitle}
      </Media.Body>
    </Media>
          </Link>
        </li>
              )
            }
          )
          :
          null}
        </ul>
      </Paper>
      );
    }
  }
}

ListList.propTypes = {
  lists: PropTypes.array,
  loading: PropTypes.bool,
  user: PropTypes.object
};

ListList.contextTypes = {
  router: PropTypes.object,
};


Add comment to this article


[Read other articles]
<=Newer article MongoDb's naming convention
=>Older article Use multiple desktop windows in Mac


Articles categorized as "Meteor by this user"
All articles of this user
Subscribe to RSS
RSS
Display Style of blog
List/Mobile(QRCode)
Term
Category
All
1.Japan
2.Atlassian's products
3.Self
4.Development of this site
5.Japanese comics
6.Japanese anime
7.Weekly hot news of Japanese culture
8.OP/ED/PV
9.Japanese game
10.Ranking
11.Japanese Comics (Manga)
12.Search Engine
13.Japanese drama
14.Japanese otaku culture
15.Programming
16.Ineternet world
17.Movie
18.C/C++
19.BerkeleyDB
20.Apache programming
21.Spam
22.Meteor
23.Marketing
24.Python
25.Scrum
26.JIRA
27.Git
28.CI
29.Jenkins
30.AWS
31.Operation
32.Singapore
33.Cloud
34.Mysql Cluster
35.DevOps
36.Bitbucket
37.Xamarin
38.Good and new
39.Monitoring
40.JavaScript(node.js)
41.React
42.Phillipines
43.Hiring
44.Python
45.SEO
46.Malaysia
47.Mongodb
48.Perl
49.Docker
50.Life hack
51.Dance
52.QA
53.Mysql
54.Digital Life Hack
55.Project management
56.Django
57.Gatsby
Sayings from S-Cry-Ed

Rule in this world is speed. Even stupid person can write cool novel if he can spend 20 years for it.

If someone helped me, I will help him in return, which is my rule.

To become stronger, consider what is cowardliest thinking. And rebel against the thinking, which will make you stronger.



I am Japanese but working for some English sites.

Doctor Job Career
Nurse Job Career
Top Page top MetaSeachJP Works