Node-postgres: Joins with overlapping column names, nested results

[nstepien]

@brianc
https://github.com/felixge/node-mysql/#joins-with-overlapping-column-names
It is possible to have a similar functionality in node-postgres?
For example:

var config = {
  text: '...',
  values: ['...'],
  nestTables: true
};
client.query(config, function(res) {
  res.rows[0].tableA.id;
  res.rows[0].tableB.id;
});

[brianc]

I definitely feel your pain on this issue, I've run into it myself. Problem is there's no really good way to fix it in postgres & I think doing that much fanciness with the return values is outside the scope of node-postgres itself.

Also: postgres unfortunately doesn't return table names in the query response, only the table OID so an additional query would be needed to look up the table names. If you have overlapping column names you can use arrayMode in your queries and it will return the results as an array instead of a JavaScript object, which should allow you to do any more fancy mapping yourself in your DAL. :+1:

[brianc]

Here's a test we have with an example of how to enable results to come back as an array:

https://github.com/brianc/node-postgres/blob/master/test/integration/client/results-as-array-tests.js#L22

If the results don't come back as an array pg will clobber one of the column values with the other one because...they're both the same name. :stuck_out_tongue_closed_eyes: Known behavior.

[nstepien]

Here's basically how I do it:

let obj1 = {...};
let obj2 = {...};
for (let key in row) {
  if (key in obj1) obj1[key] = row[key];
  if (key in obj2) obj2[key] = row[key];
}

Thankfully my overlapping columns share the same value, so I don't suffer any data loss, but it's less elegant than receiving the objects made for me as I have to redefine the table content on the js-side.
I understand the need for KISS, so I won't push this any further, thanks anyway for pg.

[nstepien]

@brianc
A friend and I played around with postgres, and it actually is possible to have nested tables in pg, for example:

let text = `SELECT to_json(a.*) AS table1, to_json(b.*) AS table2
FROM some_table a
LEFT JOIN another_table b ON a.id = b.id`;
client.query(text, function(res) {
  // ...
});

With this all rows will be structured as such:

{ table1: { ... },
  table2: { ... } }

[brianc]

Nice, yeah. I've done some stuff with row_to_json. Its just that any type
of introspection of result tables _on the client_ doesn't work for the name
of the original table. Postgres doesn't send those names to the client with
the query. In yer example you're kinda including them into the query so
they come out the other side. Definitely a good way to do it. :thumbsup:

On Sat, Feb 21, 2015 at 3:40 PM, Mayhem [email protected] wrote:

@brianc https://github.com/brianc
A friend and I played around with postgres, and it actually is possible to
have nested tables in pg, for example:

let text = SELECT row_to_json(a.*) AS table1, row_to_json(b.*) AS table2FROM some_table aLEFT JOIN another_table b ON a.id = b.id;
client.query(text, function(res) {
// ...
});

With this all rows will be structured as such:

{ table1: { ... },
table2: { ... } }

—
Reply to this email directly or view it on GitHub
https://github.com/brianc/node-postgres/issues/733#issuecomment-75388731
.

[Offirmo]

@brianc thanks mate! Your solution is the only one with 100% benefits!

For fellow googlers, this gives in knex:

return knex
    .select(knex.raw('row_to_json("users__netlify".*) AS "users__netlify", row_to_json("users".*) AS "users"')).from('users')
    .fullOuterJoin('users__netlify', {'users.id': 'users__netlify.user_id'})
    .where('users__netlify.own_id', netlify_id)
    .then(result => {
        console.log('outer join result', result)
    })