I am using Sequelize query() method as follows:
const sequelize = new Sequelize(...); ... // IMPORTANT: No changed allowed on this query const queryFromUser = "SELECT table1.colname, table2.colname FROM table1 JOIN table2 ON/*...*/"; const result = await sequelize.query(queryFromUser);
Because I am selecting two columns with identical names (colname), in the result, I am getting something like:
[{ "colname": "val1" }, { "colname": "val2" }...], and this array contains values only from the column table2.colname, as it is overwriting the table1.colname values.
I know that there is an option to use aliases in the SQL query with AS, but I don’t have control over this query.
I think it would solve the issue, if there was a way to return the result as a 2D array, instead of the array of objects? Are there any ways to configure the Sequelize query that way?
Advertisement
Answer
Im afraid this will not be possible without changes in the library directly connecting to the database and parsing its response.
The reason is:
- database returns BOTH values
- then in javascript, there is mapping of received rows values to objects
This mapping would looks something like that
// RETURNED VALUE FROM DB: row1 -> fieldName:value&fieldName:value2
// and then javascript code for parsing values from database would look similar to that:
const row = {};
row.fieldName = value;
row.fieldName = value2;
return row;
As you see – unless you change the inner mechanism in the libraries, its impossible to change this (javascript object) behaviour.
UNLESS You are using mysql… If you are using mysql, you might use this https://github.com/mysqljs/mysql#joins-with-overlapping-column-names but there is one catch… Sequelize is not supporting this option, and because of that, you would be forced to maintain usage of both libraries at ones (and both connected)
Behind this line, is older answer (before ,,no change in query” was added)
Because you use direct sql query (not build by sequelize, but written by hand) you need to alias the columns properly.
So as you saw, one the the colname would be overwritten by the other.
SELECT table1.colname, table2.colname FROM table1 JOIN table2 ON/*...*/
But if you alias then, then that collision will not occur
SELECT table1.colname as colName1, table2.colname as colName2 FROM table1 JOIN table2 ON/*...*/
and you will end up with rows like: {colName1: ..., colName2: ...}
If you use sequelize build in query builder with models – sequelize would alias everything and then return everything with names you wanted.
PS: Here is a link for some basics about aliasing in sql, as you may aliast more than just a column names https://www.w3schools.com/sql/sql_alias.asp