2

I'm trying to load and display some information from mongo db. My site runs on node.js / express.

I have two tables, the first is palettes, which i load and display all of, and that works just fine. But for each palette, i have a list of examples. I want to loop through each palette, and get all the examples for that palette.

I'm definitely doing something wrong, I think it's because the find() method is asyncronous, so my data is getting sent before it pulls the extra data. But I can't put the render function in the callback for the examples because it's run multiple times.

In PHP I used to be able to pull data from another table and connect it to the first one based on a common column, but I'm not sure how to do this here, which is why I tried to do it manually with a loop.

/* GET palette database main page. */
router.get('/', function(req, res, next) {
  console.log('rendering test page');
  var Palette = require('../models/paletteDatabase');

  //load all palettes
  Palette.find({}, function(err, palettes) {
    if (err) return next(err);

    var PaletteExamples = require('../models/paletteExamples');

    for (var i = 0; i < palettes.length; i++) {
      //split the colors into an array
      palettes[i].colorsArray = palettes[i].colors.split(',');

      PaletteExamples.find({paletteId: palettes[i]._id}, function(err, result) {
        if (err) return next(err);

        palettes[i].examples=result;


      });
    }

    res.render('palette-database', {
      title: 'Palette List',
      palettes: palettes,
      css: 'palette-db',
      js: 'palette-db'
    });

  });

});
stackers
  • 2,701
  • 4
  • 34
  • 66

2 Answers2

1

Leverage your query with the $lookup operator found in the aggregation framework. This does a left outer join to another collection in the same database to filter in documents from the "joined" collection for processing.

You could run an aggregation pipeline with this operator as follows:

/* GET palette database main page. */
router.get('/', function(req, res, next) {
    console.log('rendering test page');
    var Palette = require('../models/paletteDatabase');

    // load all palettes
    Palette.aggregate([
        {
            "$lookup": {
                "from": "paletteExamples", // <-- collection name for examples
                "localField": "_id",
                "foreignField": "paletteId",
                "as": "examples"                
            }
        }
    ]).exec(function(err, docs){

        var palettes = docs.map(function(doc){
            doc["colorsArray"] = doc.colors.split(',');
            return doc;
        });

        res.render('palette-database', {
            title: 'Palette List',
            palettes: palettes,
            css: 'palette-db',
            js: 'palette-db'
        });
    });

});

With the upcoming MongoDB 3.4 release, you can do the string split on the server by introducing the new field within a $project pipeline using the $split oprrator as follows:

// load all palettes
Palette.aggregate([
    {
        "$lookup": {
            "from": "paletteExamples",
            "localField": "_id",
            "foreignField": "paletteId",
            "as": "examples"                
        }
    },
    {
        "$project": {
            "examples": 1,
            "colorsArray": { "$split": ["$colors", ","] },
            /* project other fields as necessary */
        }
    }
]).exec(function(err, palettes){    
    res.render('palette-database', {
        title: 'Palette List',
        palettes: palettes,
        css: 'palette-db',
        js: 'palette-db'
    });
});
chridam
  • 100,957
  • 23
  • 236
  • 235
  • What is "collection name for examples", where is that defined? I don't really understand what the code is doing, but running it results in palettes being undefined – stackers Nov 15 '16 at 18:44
  • I have provided links to the documentation on the `$lookup` operator which you can refer to. The comment `"collection name for examples"` is for you to replace the value with the name of the `paletteExamples` collection i.e. the actual MongoDB collection name for the Mongoose model `paletteExamples`, which you can get from mongo shell by querying the db running the command `show collections` – chridam Nov 15 '16 at 19:15
  • I'm having trouble finding it through the console for some reason, it must be defined in my code somewhere right? Is it this from my model: var PaletteExample = mongoose.model('PaletteExample', paletteExampleSchema); – stackers Nov 16 '16 at 00:16
  • I think this [**answer**](http://stackoverflow.com/a/40417079/122005) should be useful in determining the collection names of your models. – chridam Nov 16 '16 at 08:51
  • Thanks, that was helpful. I think I've almost got it ironed out, though it's still returning a blank array for examples. I had to upgrade to mongo 3.2 to use lookup, that was fine. I located it in the database too, made sure there were entries whose example.paletteId matches a palette._id. I am able to pull both collections by themselves just fine. Here's my routers: http://pastebin.com/ibtSVNQp /test2: outputs the examples successfully, /test: sucessfully grabs the list of palettes, with example: [] on every one. Any ideas? – stackers Nov 16 '16 at 16:22
  • Turned out to be this: http://stackoverflow.com/questions/37705517/mongodb-lookup-not-working-with-id – stackers Nov 16 '16 at 20:08
  • Yup! It seems to finally be working. Thanks a lot for all your help, this was important to me. If you'd like I can throw a link to whatever you want on my site, once it's done. – stackers Nov 16 '16 at 20:40
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128298/discussion-between-chridam-and-stackers). – chridam Nov 16 '16 at 20:49
0

This is how the for loop can be removed

var array = []; //your array
PaletteExamples.find({'label':
    {$in: array}
   },function(err, examples){

   }
Ayrton Dumas
  • 95
  • 1
  • 10