Jitendra Posts - Answer Overflow

Jitendra

•Created by Jitendra on 4/15/2024 in #help

where clause when using 'with' in relational mode

Hey there. I am sqlite (turso) schema course table

export const course = sqliteTable("course", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").unique().notNull(),
  description: text("description"),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text("updated_at"),
});

export const courseRelations = relations(course, ({ many, one }) => ({
   courseMember: many(courseMember),
}));

export const course = sqliteTable("course", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").unique().notNull(),
  description: text("description"),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text("updated_at"),
});

export const courseRelations = relations(course, ({ many, one }) => ({
   courseMember: many(courseMember),
}));

courseMember

export const courseMember = sqliteTable(
  "course_member",
  {
    courseId: text("course_id")
      .notNull()
      .references(() => course.id),
    userId: text("user_id")
      .notNull()
      .references(() => user.id),
    role: text("role", { enum: ["owner", "admin", "teacher"] }).notNull(),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.courseId, table.userId] }),
    };
  }
);

export const courseMemberRelations = relations(courseMember, ({ one }) => ({
  user: one(user, {
    fields: [courseMember.userId],
    references: [user.id],
  }),
  course: one(course, {
    fields: [courseMember.courseId],
    references: [course.id],
  }),
}));

export const courseMember = sqliteTable(
  "course_member",
  {
    courseId: text("course_id")
      .notNull()
      .references(() => course.id),
    userId: text("user_id")
      .notNull()
      .references(() => user.id),
    role: text("role", { enum: ["owner", "admin", "teacher"] }).notNull(),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.courseId, table.userId] }),
    };
  }
);

export const courseMemberRelations = relations(courseMember, ({ one }) => ({
  user: one(user, {
    fields: [courseMember.userId],
    references: [user.id],
  }),
  course: one(course, {
    fields: [courseMember.courseId],
    references: [course.id],
  }),
}));

Course member is a join table between the course and userId a user can have many courses and a course can have multiple user with a role I want to fetch the courseMember and their related course. But I also want to use the search functionality. So how can I use the where clause inside with

const memberCourses = await db.query.courseMember.findMany({
    where: eq(courseMember.userId, currentUser.userId),
    with: {
      course: {
//where: 
// getting typescript error here. No `where` as suggestion from typescript
      }
}

const memberCourses = await db.query.courseMember.findMany({
    where: eq(courseMember.userId, currentUser.userId),
    with: {
      course: {
//where: 
// getting typescript error here. No `where` as suggestion from typescript
      }
}

I need your help regarding this

18 replies

Gaming

Programming