Need help transform a nested prisma query to drizzle

Hey I'm trying to translate the following prisma query to drizzle:

const invite = await prisma.invite.findUnique({
  where: {
    id: token
  },
  include: {
    invitedBy: {
      select: {
        title: true,
        firstName: true,
        lastName: true,
      }
    },
    course: {
      select: {
        name: true,
        settings: {
          select: {
            hostAlias: true,
            moderatorAlias: true,
            memberAlias: true
          }
        }
      }
    }
  }
});

const invite = await prisma.invite.findUnique({
  where: {
    id: token
  },
  include: {
    invitedBy: {
      select: {
        title: true,
        firstName: true,
        lastName: true,
      }
    },
    course: {
      select: {
        name: true,
        settings: {
          select: {
            hostAlias: true,
            moderatorAlias: true,
            memberAlias: true
          }
        }
      }
    }
  }
});

closest i've come to is this:

const invite = await db
  .select({
    inviteExpiresAt: Invite.inviteExpiresAt,
    inviteAs: Invite.inviteAs,
    limit: Invite.limit,
    invitedBy: {
      title: User.title,
      firstName: User.firstName,
      lastName: User.lastName,
    },
    course: {
      id: Course.id,
      name: Course.name,
    },
    settings: {
      hostAlias: CourseSettings.hostAlias,
      moderatorAlias: CourseSettings.moderatorAlias,
      memberAlias: CourseSettings.memberAlias
    }
  })
  .from(Invite)
  .where(eq(Invite.id, token))
  .innerJoin(User, eq(Invite.invitedById, User.id))
  .innerJoin(Course, eq(Invite.courseId, Course.id))
  .innerJoin(CourseSettings, eq(Course.id, CourseSettings.courseId))

const invite = await db
  .select({
    inviteExpiresAt: Invite.inviteExpiresAt,
    inviteAs: Invite.inviteAs,
    limit: Invite.limit,
    invitedBy: {
      title: User.title,
      firstName: User.firstName,
      lastName: User.lastName,
    },
    course: {
      id: Course.id,
      name: Course.name,
    },
    settings: {
      hostAlias: CourseSettings.hostAlias,
      moderatorAlias: CourseSettings.moderatorAlias,
      memberAlias: CourseSettings.memberAlias
    }
  })
  .from(Invite)
  .where(eq(Invite.id, token))
  .innerJoin(User, eq(Invite.invitedById, User.id))
  .innerJoin(Course, eq(Invite.courseId, Course.id))
  .innerJoin(CourseSettings, eq(Course.id, CourseSettings.courseId))

but when trying to move the settings object into the course object as it is in the prisma query I get an error

Object literal may only specify known properties, and 'hostAlias' does not exist in type 'SQL<unknown> | Aliased<unknown> | AnyMySqlColumn<{}>'

is there any way to get the same result with drizzle? thank you very much for your help! drizzle is awesome!

2 Replies

rphlmr ⚡•2y ago

I fear that we can't nest objects like that. (2 levels) You will have to refine the result in js. My project has a "repository layer" that outputs "raw" results from DB. Then I refine the resulting shape for my needs

.then(({ settings, course, ...invite }) => ({
    ...invite,
    course: { ...course, settings },
  }))

.then(({ settings, course, ...invite }) => ({
    ...invite,
    course: { ...course, settings },
  }))

philbookstOP•2y ago

oh that's sad, but at the end of the day not a big problem - thank you!

Gaming

Programming

Need help transform a nested prisma query to drizzle