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•7mo ago

@philbookst Did you ever figure this out? We're at loss on our end 😅

Sillvva•7mo 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•7mo 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•7mo 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.

Gaming

Programming

DatabaseError: Duplicate column name 'id'