nodejs-green

node: mysql column name collisions on select, join

I’ve been playing around with the MySQL Node module. I came to a place where the async javascript callback result for selects on multi-table joins started having key name collisions.

In comes nestTables:true. Wrapping the select query in an object with the nestTables property set to true will cause the result object to use nested objects for each table.

//Return Obj has name collisions
  db.query('SELECT parents.name,\
                   jobs.type, \
                   locations.name \       
            FROM parents JOIN jobs \
              ON jobs.id=parents.jobId \
                         JOIN locations \
              ON locations.id=parents.locationId',
            function(err, result){
              console.log("LOG:",result);
              //LOG: [{type:'Teacher',
              //       name:'Home'}]
          });
//Return Obj is now broken out by table with sub properties
  db.query({sql: \
           'SELECT parents.name,\
                   jobs.type,\
                   locations.name\
            FROM parents JOIN jobs \
              ON jobs.id=parents.jobId \
                         JOIN locations \
              ON locations.id=parents.locationId',
            nestTables:true},
            function(err, result){
              console.log("LOG:",result);
              //LOG: [{parents:{name: "Joe"},
              //       jobs: {type:'Teacher'},
              //       locations:{name:'Home'}}]
          });

Note: you can also use nestedTable: "_" to force the result to use TableName_ColumnName as the object keys.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>