Drizzle Team•2y ago

Query/select statement of column via sql`` for extras field

is it possible to make a normal select to another table inside a extras object in a relational query? i'm trying to do something like this:

const lessonWithComments = await db.query.Lesson.findFirst({
  where: eq(Lesson.id, lessonId),
  with: {
    comments: {
      with: {
        reactions: true,
        user: {
          columns: {
            id: true,
            title: true,
            firstName: true,
            lastName: true,
            image: true
          },
          extras: {
            memberKind: sql`
              SELECT ${MemberOnCourse.memberKind}
              FROM ${MemberOnCourse}
              WHERE ${MemberOnCourse.userId} = ${User.id}
            `.as("memberKind")
          }
        }
      }
    }
  },
  orderBy: (Comment, { asc }) => [asc(Comment.createdAt)]
});

const lessonWithComments = await db.query.Lesson.findFirst({
  where: eq(Lesson.id, lessonId),
  with: {
    comments: {
      with: {
        reactions: true,
        user: {
          columns: {
            id: true,
            title: true,
            firstName: true,
            lastName: true,
            image: true
          },
          extras: {
            memberKind: sql`
              SELECT ${MemberOnCourse.memberKind}
              FROM ${MemberOnCourse}
              WHERE ${MemberOnCourse.userId} = ${User.id}
            `.as("memberKind")
          }
        }
      }
    }
  },
  orderBy: (Comment, { asc }) => [asc(Comment.createdAt)]
});

i'm getting a syntax error at position 2270 near 'SELECT' is this possible or should i just add the necessary relation and query it on the same level as the user object?

1 Reply

Dan•2y ago

try wrapping your SELECT query in parenthesis, like this:

sql`(select ... )`.as("memberKind")

sql`(select ... )`.as("memberKind")

Gaming

Programming

Query/select statement of column via sql`` for extras field