DatabaseError: Duplicate column name 'id'

hey guys, i have a problem in my query but cannot figure out what is causing it.
const coursesSq = db
.select({
id: Course.id,
name: Course.name,
image: Course.image,
status: Course.status,
description: Course.description,
guides: Course.guides,
price: Course.price,
moduleCount: sql<number>`COUNT(${Module.id})`.as("moduleCount")
})
.from(Course)
.leftJoin(Module, eq(Course.id, Module.courseId))
.groupBy(Course.id)
.as("courses");

const membersOnCourse = db
.select({
memberId: MemberOnCourse.id,
courseId: MemberOnCourse.courseId,
memberKind: MemberOnCourse.memberKind,
user: {
id: User.id,
title: User.title,
firstName: User.firstName,
lastName: User.lastName,
image: User.image
}
})
.from(MemberOnCourse)
.innerJoin(User, eq(MemberOnCourse.userId, User.id))
.as("membersOnCourse");

const courses = await db
.select()
.from(coursesSq)
.leftJoin(membersOnCourse, eq(coursesSq.id, membersOnCourse.courseId));
const coursesSq = db
.select({
id: Course.id,
name: Course.name,
image: Course.image,
status: Course.status,
description: Course.description,
guides: Course.guides,
price: Course.price,
moduleCount: sql<number>`COUNT(${Module.id})`.as("moduleCount")
})
.from(Course)
.leftJoin(Module, eq(Course.id, Module.courseId))
.groupBy(Course.id)
.as("courses");

const membersOnCourse = db
.select({
memberId: MemberOnCourse.id,
courseId: MemberOnCourse.courseId,
memberKind: MemberOnCourse.memberKind,
user: {
id: User.id,
title: User.title,
firstName: User.firstName,
lastName: User.lastName,
image: User.image
}
})
.from(MemberOnCourse)
.innerJoin(User, eq(MemberOnCourse.userId, User.id))
.as("membersOnCourse");

const courses = await db
.select()
.from(coursesSq)
.leftJoin(membersOnCourse, eq(coursesSq.id, membersOnCourse.courseId));
any idea what's going on?
4 Replies
PapaFinn
PapaFinn3mo ago
@philbookst Did you ever figure this out? We're at loss on our end 😅
Sillvva
Sillvva3mo ago
Try getting the raw sql for the query at issue and printing it out, or use the logger. https://orm.drizzle.team/docs/goodies#printing-sql-query https://orm.drizzle.team/docs/goodies#logging
Drizzle ORM - Goodies
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
philbookst
philbookst3mo ago
i'm sorry i don't quite remeber, it was almost a year ago i do remeber printing out the raw sql and asking chatgpt whats wrong 😄
PapaFinn
PapaFinn3mo ago
We figured it out! It was a fundamental misunderstanding on our end with how SQL works with subqueries. To fix it, we did need to reach for some custom sql strings.