Diferent results on SELECT query

[jenanni]

I have a table "users" whit this rows:

user_id	user_name
1	Jhon
2	Paul

And other table "tests" with this rows:

test_id	created_by_user_id	modified_by_user_id
1	1	2

at psql shell, I send the following query:

SELECT test_id, created.user_name, modified.user_name FROM tests LEFT JOIN users as created ON tests.created_by_user_id = created.user_id LEFT JOIN users as modified ON tests.modified_by_user_id = modified.user_id

as expected, I have thr following result:

test_id	created	modified
1	Jhon	Paul

but using pg-node whit the same query as:

const result = await client.query('SELECT test_id, created.user_name, modified.user_name FROM tests LEFT JOIN users as created ON tests.created_by_user_id = created.user_id LEFT JOIN users as modified ON tests.modified_by_user_id = modified.user_id')

in result.rows I have this:

test_id	created	modified
1	Jhon	Jhon

[joelmukuthu]

Hi, that's happening because you haven't used aliases in your return fields. This is what the query returns (both in psql and when it's run by node-pg):

test_id  | user_name | user_name 
---------+-----------+-----------
       1 | Jhon      | Paul

When node-pg parses that row, it finds two columns both named user_name, and because those get assigned to an object, the second one replaces the first one. So what you get in result.rows is actually:

 [ { test_id: 1, user_name: 'Paul' } ]

You could either:

1. add aliases to the return fields in your query (created.user_name AS created_by, modified.user_name AS modified_by):

const result = await client.query({ 
  text: 'SELECT test_id, created.user_name AS created_by, modified.user_name AS modified_by FROM tests LEFT JOIN users as created ON tests.created_by_user_id = created.user_id LEFT JOIN users as modified ON tests.modified_by_user_id = modified.user_id'
});

// result.rows:
// [ { test_id: 1, created_by: 'Jhon', modified_by: 'Paul' } ]

2. or configure the query to return arrays instead of objects (rowMode: 'array'):

const result = await client.query({ 
  text: 'SELECT test_id, created.user_name, modified.user_name FROM tests LEFT JOIN users as created ON tests.created_by_user_id = created.user_id LEFT JOIN users as modified ON tests.modified_by_user_id = modified.user_id',
  rowMode: 'array'
});

// result.rows:
// [ [ 1, 'Jhon', 'Paul' ] ]

[jenanni]

Hi Joel, thanks for your response. I'm using alias rigth now, but the same results....

const result = await client.query('SELECT test_id, created.user_name as created_user, modified.user_name as modified_user FROM tests LEFT JOIN users as created ON tests.created_by_user_id = created.user_id LEFT JOIN users as modified ON tests.modified_by_user_id = modified.user_id')

and this is the result.

test_id	created_user	modified_user
1	Jhon	Jhon

[joelmukuthu]

There must be something else going on because I tested it with the data you provided and can’t see the results you’re seeing. Is there some more information you haven’t provided here? Does the query yield a different result when run in psql? It might also be worthwhile taking a look at the tables (especially tests) and confirming that the data is as you expect it to be.

[jenanni]

Thanks Joel! It is solved. As you said it was a problems with the tables.

[joelmukuthu]

Great to hear, you’re welcome.