drizzle-team
[BUG]: Aliasing the same table twice and then inner joining one while left joining the other leads to type never
What version of drizzle-orm are you using?
0.31.1
What version of drizzle-kit are you using?
No response
Describe the Bug
If I make two table aliases for the same table and then inner join one while left joining the other, the return type of my query ends up being never[].
const fromCompany = aliasedTable(company, "fromCompany");
const toCompany = aliasedTable(company, "toCompany");
const data = await db
.select({
referral,
fromCompany,
toCompany,
})
.from(referral)
.innerJoin(fromCompany, eq(fromCompany.id, referral.fromCompanyId))
.leftJoin(toCompany, eq(toCompany.id, referral.toCompanyId));
Unfortunately the type of data becomes:
const data: never[]
I've reduced the Company table to the smallest it could possibly be but it makes absolutely no difference:
export const company = mysqlTable(
"Company", {
id: int("id").primaryKey().autoincrement(),
}
);
Interestingly, if both joins to the Company table are left joins or if both joins are inner joins, it works fine, but that's not my intended behavior.
// So this works
const data = await db
.select({
referral,
fromCompany,
toCompany,
})
.from(referral)
.leftJoin(fromCompany, eq(fromCompany.id, referral.fromCompanyId))
.leftJoin(toCompany, eq(toCompany.id, referral.toCompanyId));
// And so does this
const data = await db
.select({
referral,
fromCompany,
toCompany,
})
.from(referral)
.innerJoin(fromCompany, eq(fromCompany.id, referral.fromCompanyId))
.innerJoin(toCompany, eq(toCompany.id, referral.companyId));
Types aside, my query works as intended at runtime with one inner join and one left join.
Expected behavior
I expect that I can alias the same table twice and be able to inner join one while left joining the other.
Environment & setup
Typescript: 5.4.5
I'm having the same issue.
const player1 = aliasedTable(players, 'player1')
const player2 = aliasedTable(players, 'player2')
return db
.select({
id: matches.id,
player1: player1.name,
player2: player2.name,
winnerId: matches.winnerId,
player1Id: matches.player1Id,
})
.from(matches)
.innerJoin(player1, eq(matches.player1Id, player1.id))
.leftJoin(player2, eq(matches.player2Id, player2.id))
.where(eq(matches.roundId, roundId))
Try using the alias function instead of aliasedTable alias can be imported from "drizzle-orm/sqlite-core' or MySQL core
Try using the alias function from instead of aliasedTable alias can be imported from "drizzle-orm/sqlite-core' or MySQL core
that worked for me
That indeed works. If you're using postgres:
import { alias } from 'drizzle-orm/pg-core'
It does, in fact work! Thanks for the insight everyone!
That is to say, use alias. Do not use aliasedTable.
aliasedTable is only for use in the RQB API. I see that the docs uses that function instead of alias: https://orm.drizzle.team/docs/joins#aliases--selfjoins.
We'll fix this documentation issue.
Fixing docs now, sorry for late response here
Is it fixed on docs? I'm still seeing the old guidance. https://orm.drizzle.team/docs/joins#aliases--selfjoins
aliasedTableis only for use in the RQB API. I see that the docs uses that function instead ofalias: https://orm.drizzle.team/docs/joins#aliases--selfjoins.We'll fix this documentation issue.
Still not fixed, thanksfully I found this GH issue since I was VERY confused.
