Referring to columns by their field index instead of name

[akayami]

Hi,

I've seen this feature in PHP drivers (PDO fetch_style parameter PDO::FETCH_NUM) where one could refer to the columns in the result set by their index position within the "fields" array. I was not able to find this in node-mysql driver. The use-case for it is when someone runs a join on 2 tables, and both table contain the same field name, the current row object only contains one value because field names collide. I know that one may use aliasing to distinguish the 2, but that can be problematic/cumbersome particularly when faced with pragmatically generated queries.

Referring to columns by first analyzing the fields array is an elegant solution for this. Currently when one runs a join with select *, the key count in each row is different that the count on fields array.

conn.query('select * from A join B...' , function(err, result, fields) {
      if(Object.keys(result[0]).length != fields.length) {
            console.log('Field name collision occured');
      }
});

This creates a problem as the fields array becomes "misaligned" with the result row, and therefore makes any references between the field details and actual values difficult.

I would suggest something along the lines of an options object passed optionally after values:

conn.query('select * from A join B ON A.id=B.A_id WHERE A.id=?', [1], {useArrayRows: true}, function(err, result, fields) {
           var columnIndex = 0;
           console.log('The value of ' + fields[columnIndex].orgTable + '.' + fields[columnIndex].name + ' is ' result[0][columnIndex]);
});

Thanks

[dougwilson]

Hi, he feature you are describing does not exist, no. You can always use the nest_tables option as described in https://github.com/felixge/node-mysql#joins-with-overlapping-column-names if that's doable. Otherwise #770 is the feature request for indexed result objects if you are interested in contributing the feature :)

[lroal]

+1.
I was searching for the exact same feature. node-pg has this, it's called rowMode='array' .