DT
Drizzle Team•9mo ago
Jitendra

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
5 Replies
Jitendra
JitendraOP•9mo ago
basically it's many to many relationship. I have seen example of one to many in the docs using where clause in with. Even one to many works for me But in this case I am not able to use the where clause bump.. Anyone please Sorry for tagging but I couldn't find any help @Angelelz @Dan Kochetov
Angelelz
Angelelz•9mo ago
Feel free to tag me whenever you need Let me take a look at your cose Code Ok, so the error you see is because a courseMember is related to only one course when seen from the connecting table. The where doesn't show up, because it will be only one row, you either want it or not Can you explain that you would like to get from this query and I can help you put it together
Jitendra
JitendraOP•9mo ago
Thank you so much Angelelz for the reply. I want all the courses of the particular member. I am able to achieve this 
  const memberCourses = await db.query.courseMember.findMany({
    where: eq(courseMember.userId, currentUser.userId),
    with: {
      course: {
        columns: {
          id: true,
          title: true,
          slug: true,
        },
      },
    },
  });
  const memberCourses = await db.query.courseMember.findMany({
    where: eq(courseMember.userId, currentUser.userId),
    with: {
      course: {
        columns: {
          id: true,
          title: true,
          slug: true,
        },
      },
    },
  });
Response: 
memberCourses [
  {
    courseId: 'omfwymi9a6ymi3qkdvaqohoy',
    userId: 'w013zk4be8rnw4wzdmmuoc6x',
    role: 'owner',
    course: {
      id: 'omfwymi9a6ymi3qkdvaqohoy',
      title: 'React Beginners Course',
      slug: 'react-beginners-course'
    }
  },
  {
    courseId: 'mq8gu6j222h9znq72ipj2f8w',
    userId: 'w013zk4be8rnw4wzdmmuoc6x',
    role: 'owner',
    course: {
      id: 'mq8gu6j222h9znq72ipj2f8w',
      title: 'React UI Library',
      slug: 'react-ui-library'
    }
  },
  {
    courseId: 'mzj6i75a3m4ofnnsvyoc66xo',
    userId: 'w013zk4be8rnw4wzdmmuoc6x',
    role: 'owner',
    course: {
      id: 'mzj6i75a3m4ofnnsvyoc66xo',
      title: 'Advanced UI',
      slug: 'advanced-ui'
    }
  }
]
memberCourses [
  {
    courseId: 'omfwymi9a6ymi3qkdvaqohoy',
    userId: 'w013zk4be8rnw4wzdmmuoc6x',
    role: 'owner',
    course: {
      id: 'omfwymi9a6ymi3qkdvaqohoy',
      title: 'React Beginners Course',
      slug: 'react-beginners-course'
    }
  },
  {
    courseId: 'mq8gu6j222h9znq72ipj2f8w',
    userId: 'w013zk4be8rnw4wzdmmuoc6x',
    role: 'owner',
    course: {
      id: 'mq8gu6j222h9znq72ipj2f8w',
      title: 'React UI Library',
      slug: 'react-ui-library'
    }
  },
  {
    courseId: 'mzj6i75a3m4ofnnsvyoc66xo',
    userId: 'w013zk4be8rnw4wzdmmuoc6x',
    role: 'owner',
    course: {
      id: 'mzj6i75a3m4ofnnsvyoc66xo',
      title: 'Advanced UI',
      slug: 'advanced-ui'
    }
  }
]
But when I type the search query I want only matching course name so I want to use the where clause with like..
Angelelz
Angelelz•9mo ago
In that case I would do it like this: 
  const memberCourses = await db.query.courseMember.findMany({
    where: and(eq(courseMember.userId, currentUser.userId),inArray(
        db.select({id: course.id}).from(course).where(eq(course.title, "the title I want")), courseMember.courseId
    )),
    with: {
      course: {
        columns: {
          id: true,
          title: true,
          slug: true,
        },
      },
    },
  });
  const memberCourses = await db.query.courseMember.findMany({
    where: and(eq(courseMember.userId, currentUser.userId),inArray(
        db.select({id: course.id}).from(course).where(eq(course.title, "the title I want")), courseMember.courseId
    )),
    with: {
      course: {
        columns: {
          id: true,
          title: true,
          slug: true,
        },
      },
    },
  });
Basically you are using a subquery inside the where. Pulling the courseId that corresponds to the title you need, and then search for that courseId Btw, you could include metadata in the connecting table. For example, you can put the course title there, it would make the query a lot easier This is an interesting technique but you have to try to not abuse it
Jitendra
JitendraOP•9mo ago
Thank you so much Angelelz. This works for me. Also thanks for suggestion for having title in the join table. I will try to explore more inArray thing. Just a little correction the courseMember.courseId will be first arguement 
 inArray(
        courseMember.courseId,
        db
          .select({ id: course.id })
          .from(course)
          .where(like(course.title, `%${search ? search : ""}%`))
      )
 inArray(
        courseMember.courseId,
        db
          .select({ id: course.id })
          .from(course)
          .where(like(course.title, `%${search ? search : ""}%`))
      )
Again thank you for support. Really Appreciate it
Want results from more Discord servers?
Add your server