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

i'm new node lands of c#, php , python. i've been working days in many variations of same problem - how can retrieve set of data, based on data, retrieve set, render results out. i've tried method below, event based (client.on("row")) , async module , can't produce right results. in end, i'd pass projects object tasks added express render.

could me out of 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 * project", function(err, projects) {                (var i=0; i<projects.rowcount; i++) {                 var project = projects.rows[i];                  client.query("select * task 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 solution issue, share information, in end, below code little touch 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 each project row     client.query("select * task project_id="+projectrow.id, function(err, result) {         console.log("tasks");       if(err) return cb(err); // let async know there error. further processing stop       projectrow.tasks = result.rows;       cb(null); // no error, continue next projectrow, if     });   };    client.connect(function(err) {       client.query("select * 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);           // project rows have been handled           console.log(projects.rows);           res.render('main', { title: 'my projects', projects: projects.rows});         });       });     }); } }; 

you need familiarize asynchronous flow-control. can tricky because async functions (postgres queries in case) execute right after in same turn of event loop, while results come trickling in in subsequent turns.

for code example, means i set projects.rowcount-1 , project set projects.rows[project.rowcount-1] instantly, while queries have been queued up. stay after result queries come in. not want.

the quickes solution use async library. https://github.com/caolan/async . handle tedious bean-counting you.

for particular example, you'd replace code within client.connect callback like

  addtaskstoproject = function(projectrow, cb) { // called once each project row     client.query("select * task project_id="+projectrow.id, function(err, result) {       if(err) return cb(err) // let async know there error. further processing stop       projectrow.tasks = result.rows       cb(null) // no error, continue next projectrow, if     }   }   client.query("select * project", function(err, projects) {     if (err) return console.error(err)     async.each(projects.rows, addtaskstoproject, function(err) {       if (err) return console.error(err)       // project rows have been handled       res.render('main', { title: 'my projects', projects: project.rows});     })   } 

note because how javascript object references work, objects part of project.rows array modified in place. wouldn't case if you'd try assign new value projectrow variable.