Drizzle Team•6mo ago

working with many to many

I have a user table, course table along with the course_member which is a join table. How to get all the courses where userId is provided Tables schema along with relation

export const user = sqliteTable("user", {
  id: text("id")
    .$default(() => createId())
    .primaryKey(),
  userName: text("user_name").notNull().unique(),
  email: text("email").notNull().unique(),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
});

export const userRelations = relations(user, ({ one, many }) => ({
  courseMember: many(courseMember),
}));

export const course = sqliteTable("course", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").unique().notNull(),
});

export const courseRelations = relations(course, ({ many, one }) => ({
  courseMember: many(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 user = sqliteTable("user", {
  id: text("id")
    .$default(() => createId())
    .primaryKey(),
  userName: text("user_name").notNull().unique(),
  email: text("email").notNull().unique(),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
});

export const userRelations = relations(user, ({ one, many }) => ({
  courseMember: many(courseMember),
}));

export const course = sqliteTable("course", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").unique().notNull(),
});

export const courseRelations = relations(course, ({ many, one }) => ({
  courseMember: many(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],
  }),
}));

1 Reply

Jsx•6mo ago

currently I am doing this

  const memberCourses = await db.query.courseMember.findMany({
    where: eq(courseMember.userId, currentUser.userId),
    with: {
      course: {
        columns: {
          id: true,
          title: true,
          slug: true,
        },
      },
    },
  });

  const courses: CourseData[] = [];
  memberCourses.forEach((orgCourse) => courses.push(orgCourse.course));

  const memberCourses = await db.query.courseMember.findMany({
    where: eq(courseMember.userId, currentUser.userId),
    with: {
      course: {
        columns: {
          id: true,
          title: true,
          slug: true,
        },
      },
    },
  });

  const courses: CourseData[] = [];
  memberCourses.forEach((orgCourse) => courses.push(orgCourse.course));

Okay so this is redundant coz I am again mapping courses to display so I can directly do this I can remove the courses array

     {memberCourses?.map((memberCourse) => (
          <div key={memberCourse.courseId} className="border-2 px-5 py-3">
            <h3 className="text-xl font-medium">{memberCourse.course.title}</h3>
            <Link href={`/dashboard/courses/${memberCourse.course.slug}/basic`}>
              Manage
            </Link>
          </div>
        ))}

     {memberCourses?.map((memberCourse) => (
          <div key={memberCourse.courseId} className="border-2 px-5 py-3">
            <h3 className="text-xl font-medium">{memberCourse.course.title}</h3>
            <Link href={`/dashboard/courses/${memberCourse.course.slug}/basic`}>
              Manage
            </Link>
          </div>
        ))}

Gaming

Programming

working with many to many