Cannot join tables where the columns name are the same.
If you do then the join table will overwrite the from table. it is really frustrating
19 Replies
did you ever solve this?
There is a work around use the sql`` raw where you need to name your columns differently
Omg thanks so much
I was stuck with this
No need to use raw sql I think. You just need to specify the columns that you're selecting explicitly.
If the column names are the same then it has a fit
the problem is a bit more complicated. It's a problem with D1 itself but the extra abstraction drizzle provides made it difficult to track down. I was recommended to alias the selected columns in a way that would not collide for each other. Turns out drizzle does not have a way to easily alias select so I ended up with this monstrosity
It would be nice if this could have been avoided all together. That is not at all a problem with the
postgress-js backend
I could create an issue with a more detailed reproductions stepsI think I saw one
Bingo, thanks for detailing it out 🎉
But honestly my experience with drizzle sqlite was horrible. I had to do this mess and I had to write my own
boolean typeIs it related to this? https://github.com/drizzle-team/drizzle-orm/issues/555
GitHub
[BUG]: Broken shifted columns with leftJoin and same column name (o...
What version of drizzle-orm are you using? 0.25.4 What version of drizzle-kit are you using? 0.17.6 Describe the Bug I got broken results with leftJoin with same columns. Details My schema (partial...
Yes, very much
Although I heard on d1 support discord that this is intended (this overwrite, or at least that is “not specific to d1”)
Would it be a libsql thing then? Like does Turso have the same behavior?
Curious on the boolean type thing.
Can drizzle perhaps remap this automatically as I did to fix this?
Not the ideal solution, but better the what i did
Based on the PR, it could be D1 specific. Rather it appears that they intentionally let it overwrite https://github.com/cloudflare/workerd/pull/911/files
I feel like I don’t fully understand the issue. Did something like the following not work for you?
const firstAlias = aliasedTable(myTable, "firstAlias")
const secondAlias = aliasedTable(myTable, "secondAlias")
const fetchedData = drizzleClient
.select({
first: getTableColumns(firstAlias),
second: getTableColumns(secondAlias)
})
.from(myTable)
.innerJoin(firstAlias, eq(firstAlias.someColumn, someValue))
.leftJoin(secondAlias, eq(secondAlias.someColukn, someOtherValue))
Sorry for mobile formatting 😅
that did not work for me
Could you please share your findings and workaround in that issue?