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

6 Replies

Jitendra•7mo 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•7mo 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

netwrx•7mo ago

i believe i have a similar issue https://discord.com/channels/1043890932593987624/1230519866113654785 do you think you could share some insight 😓

Jitendra•7mo 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•7mo 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•7mo 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

Gaming

Programming

where clause when using 'with' in relational mode