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

Question

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});
        });
      });
    });
}
};

Myrne Stol · Accepted Answer · 2013-05-09 19:41:22Z

1

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.

answered May 9, 2013 at 19:41

Myrne Stol

11.5k4 gold badges42 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brandon Over a year ago

I also want to point out that the entire for loop could be avoided if the first query returned the tasks joined to the projects. But that doesn't answer the question, so I'll leave it as a comment.

Jason Hoekstra Over a year ago

Thanks Brandon - I considered that as well, but really posted the question to understand how to do nested operations as opposed to best solution for this particular query. Noted however, I'll restructure for that now I understand the pattern.

Jason Hoekstra Over a year ago

Meryn - many thank yous! That did the trick and showed me a way to correctly structure this solution. Much appreciated!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related