CODEWITHSUNDEEP
node.jsexpress
Jason Hoekstra
Jason Hoekstra

Reputation: 162

Node.js - Query within a query, then render results using Express

I'm new to Node from the lands of C#, PHP and Python. I've been working days in many variations of the same problem - how can I retrieve a set of data, based on that data, retrieve another set, then render the results out. I've tried the method below, event based (client.on("row")) and the async module and I can't get any to produce the right results. In the end, I'd like to pass a projects object with tasks added to Express to render.

Could anyone help me out of this hole?

exports.index = function(req, res){
req.session.user_id = 1;
if (req.session == undefined || req.session.user_id == null || req.session.user_id < 0) {
    res.redirect('/login');
} else {
    var pg = require('pg');
    var conString = "postgres://jason@localhost:5432/simpleproject";

    var client = new pg.Client(conString);

    client.connect(function(err) {
        client.query("SELECT * FROM project", function(err, projects) {



            for (var i=0; i<projects.rowCount; i++) {
                var project = projects.rows[i];

                client.query("SELECT * FROM task WHERE project_id="+projects.rows[i].id, function(err, subrows) {

                    if (subrows.rowCount > 0) {
                        project.tasks = subrows.rows;
                        console.log("adding tasks");
                    } else {
                        project.tasks = null;
                    }

                    if (i==projects.rowCount) {
                    console.log("rendering");
                    res.render('main', { title: 'My Projects', projects: projects });
                    }

                });
            }
            if (err != null) { console.log(err); }
        }
        );
    });     
}

};

UPDATE: Meryn below provides a good solution to my issue, just to share that information, in the end, below his code with a little touch up to get it to operate: (thanks Meryn!)

var async = require('async');

exports.index = function(req, res){
req.session.user_id = 1;
if (req.session == undefined || req.session.user_id == null || req.session.user_id < 0) {
    res.redirect('/login');
} else {
    var pg = require('pg');
    var conString = "postgres://jason@localhost:5432/simpleproject";

    var client = new pg.Client(conString);

    var addTasksToProject = function(projectRow, cb) { // called once for each project row
    client.query("SELECT * FROM task WHERE project_id="+projectRow.id, function(err, result) {
        console.log("tasks");
      if(err) return cb(err); // let Async know there was an error. Further processing will stop
      projectRow.tasks = result.rows;
      cb(null); // no error, continue with next projectRow, if any
    });
  };

  client.connect(function(err) {
      client.query("SELECT * FROM project", function(err, projects) {
        console.log("projects");
        if (err) return console.error(err);
        async.each(projects.rows, addTasksToProject, function(err) {
          if (err) return console.error(err);
          // all project rows have been handled now
          console.log(projects.rows);
          res.render('main', { title: 'My Projects', projects: projects.rows});
        });
      });
    });
}
};

Upvotes: 0

Views: 4202

Answers (1)

Myrne Stol
Myrne Stol

Reputation: 11448

You need to familiarize yourself with asynchronous flow-control. It can be tricky because the async functions (postgres queries in this case) will execute right after another in the same turn of the event loop, while the results come trickling in in subsequent turns.

For your code example, this effectively means that i will be set to projects.rowCount-1 and project will be set to projects.rows[project.rowCount-1] almost instantly, while the queries have been queued up. They stay like this after the result for the queries come in. Not what you want.

The quickes solution is to use the Async library. https://github.com/caolan/async . This will handle the tedious bean-counting for you.

For this particular example, you'd replace the code within the client.connect callback with something like

  addTasksToProject = function(projectRow, cb) { // called once for each project row
    client.query("SELECT * FROM task WHERE project_id="+projectRow.id, function(err, result) {
      if(err) return cb(err) // let Async know there was an error. Further processing will stop
      projectRow.tasks = result.rows
      cb(null) // no error, continue with next projectRow, if any
    }
  }
  client.query("SELECT * FROM project", function(err, projects) {
    if (err) return console.error(err)
    async.each(projects.rows, addTasksToProject, function(err) {
      if (err) return console.error(err)
      // all project rows have been handled now
      res.render('main', { title: 'My Projects', projects: project.rows});
    })
  }

Note that because how Javascript object references work, the objects part of the project.rows array will be actually modified in place. This wouldn't be the case if you'd actually try to assign a new value to the projectRow variable.

Upvotes: 1

Related Questions