Joins with same column names are lost

[joshstrange]

I'm joining on the same table twice with 2 different alias like this:

SELECT t.id, fromUser.firstName, toUser.firstName, ...
FROM transactions t
INNER JOIN users fromUser ON fromUser.id = t.fromUserId
INNER JOIN users toUser ON toUser.id = t.toUserId
WHERE t.fromUserId = :userId OR t.toUserId = :userId

The problem is my later column names with the same name are being clobbered. I ran the query again with hydrateColumnNames: false and I can see all the data coming back. After renaming my columns (toUser.firstName as toUserFirstName) my data is returning but I was wondering if there was some way to support returning queries like this in a nested format:

{
    t: {id: '....', ....},
    fromUser: {firstName: '...', ....}
    toUser: {firstName: '...', ....}
}

Or, in order to not add more maintenance, a way to provide a custom de-serializer function for my data so that I could write a custom one that would accomplish the above format when joining (to nest each table results under it's alias or name if no alias is provided).

[ffxsam]

I'd say that SELECT a.someColumn, b.someColumn is bad practice anyway, and should always be aliased, so my gut is to not modify this library to support it. It's definitely an interesting idea though! Just not something I can make time for, when there are so many other things I'd change before this, like proper handling of Postgres arrays, or auto-translating Postgres dates to ISO8601, etc.

[joshstrange]

I totally understand not seeing this as a high priority. I'm not sure I have ever seen this referred to as a bad practice since MySQL itself supports this without issue (just returning the column names as a.someColumn, b.someColumn) but if there is document/blog/etc that advises against that I'd be interested in see it. I've been using that practice for well over a decade now and most ORMs (I know this is not an ORM) have some method of support it with the raw a.someColumn name or nesting it under the top-level FROM X object. I'm not saying you are wrong, god knowns I've done other things the wrong way for longer than a decade but I am interested in learning more on it.

I've worked around it for now by just aliasing all the columns with the same name, thank you!

[ffxsam]

I am by no means a database wiz either, BTW. 😉 Just seems to me that it's a good practice to alias the column names for the sake of legibility, and to reduce confusion.