Theo's Typesafe Cult•2y ago

where clause in drizzle insert

 await db
    .insert(trackingQuestions)
    .values({
      questionNumber: questionNumber,
      userId: session.user.id,
    })
    .where(
      notExists(
        db
          .select()
          .from(trackingQuestions)
          .where(
            and(
              eq(trackingQuestions.questionNumber, questionNumber),
              eq(trackingQuestions.userId, session.user.id)
            )
          )
      )
    );

 await db
    .insert(trackingQuestions)
    .values({
      questionNumber: questionNumber,
      userId: session.user.id,
    })
    .where(
      notExists(
        db
          .select()
          .from(trackingQuestions)
          .where(
            and(
              eq(trackingQuestions.questionNumber, questionNumber),
              eq(trackingQuestions.userId, session.user.id)
            )
          )
      )
    );

can anyone tell why it's giving typescript error and how can i resolve it error: where clause doesn't exists i want to do this query with single query

4 Replies

whatplan•2y ago

i am willing to help if you provide more information: please send the table schema, the typescript error you are getting and where it is showing up, and a explanation of what you are attempting to do with the query

Sylte•2y ago

Instead of this where clause, another possibility could also be a unique index?

pradeepOP•2y ago

i want do give below thing in one query:- if (condition) then don't insert in table else insert it into table

export const trackingQuestions = mysqlTable("tracking_questions", {
  id: int("id").notNull().autoincrement().primaryKey(),
  createdAt: timestamp("createdAt").defaultNow(),
  questionNumber: int("questionNumber").notNull(),
  userId: varchar("userId", { length: 255 }).notNull(),
});

export const trackingQuestions = mysqlTable("tracking_questions", {
  id: int("id").notNull().autoincrement().primaryKey(),
  createdAt: timestamp("createdAt").defaultNow(),
  questionNumber: int("questionNumber").notNull(),
  userId: varchar("userId", { length: 255 }).notNull(),
});

schema error is saying you cann't add where clause currently i am doing achiving this by

 const questions = await db
    .select()
    .from(trackingQuestions)
    .where(
      and(
        eq(trackingQuestions.questionNumber, questionNumber),
        eq(trackingQuestions.userId, session.user.id)
      )
    );
  if (questions.length === 0) {
    await db.insert(trackingQuestions).values({
      questionNumber: questionNumber,
      userId: session.user.id,
    });
  }

 const questions = await db
    .select()
    .from(trackingQuestions)
    .where(
      and(
        eq(trackingQuestions.questionNumber, questionNumber),
        eq(trackingQuestions.userId, session.user.id)
      )
    );
  if (questions.length === 0) {
    await db.insert(trackingQuestions).values({
      questionNumber: questionNumber,
      userId: session.user.id,
    });
  }

whatplan•2y ago

oh I see yea I would try to avoid the nested db calls no reason to not seperate the reading data then writing data other than to make things more confusing

Gaming

Programming

where clause in drizzle insert