gfung Posts - Answer Overflow

gfung

•Created by gfung on 4/14/2024 in #help

Updating multiple rows with single SQL query doesn't work.

await db.execute(
      sql`UPDATE users SET approved = true WHERE id IN (${ids
        .map((id) => `'${id}'`)
        .join(', ')})`
    );

await db.execute(
      sql`UPDATE users SET approved = true WHERE id IN (${ids
        .map((id) => `'${id}'`)
        .join(', ')})`
    );

With the above code, I'm setting the approved column to be true. ids is an array of strings, that I convert to the form of 'id1', 'id2'. Passing a single value into ids works, but when more than one value is passed, nothing happens. There is no error returned. Bizarrely, when I console log the generated sql query (in the case of multiple IDs) and execute it manually on my database, everything works fine. I suspect it's something to do with the sql function, but I don't understand why it works when there's only one ID.

4 replies

DTDrizzle Team

•Created by gfung on 1/26/2024 in #help

Column _ cannot be cast automatically to type integer

I have set a postId column in my comments table, in which I store the ID of the post the comment is attached too. posts.id is of type serial, and I set postId to be an integer, but I keep getting the error column "postId" cannot be cast automatically to type integer with hint You might need to specify "USING "postId"::integer". How can I fix this? Full code:

export const posts = pgTable('post', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('createdAt').defaultNow(),
  title: text('title').notNull(),
  content: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  userId: integer('userId')
    .notNull()
    .references(() => users.id),
  topic: topicsEnum('topics'),
  category: categoriesEnum('categories')
});

export const postRelations = relations(posts, ({ one, many }) => {
  user: one(users, {
    fields: [posts.userId],
    references: [users.id]
  });
  comments: many(comments);
});

export const comments = pgTable('comment', {
  id: serial('id').primaryKey(),
  comment: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  postId: integer('postId')
    .notNull()
    .references(() => posts.id),
  createdAt: timestamp('createdAt').defaultNow()
});

export const commentRelations = relations(comments, ({ one }) => {
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id]
  });
});

export const posts = pgTable('post', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('createdAt').defaultNow(),
  title: text('title').notNull(),
  content: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  userId: integer('userId')
    .notNull()
    .references(() => users.id),
  topic: topicsEnum('topics'),
  category: categoriesEnum('categories')
});

export const postRelations = relations(posts, ({ one, many }) => {
  user: one(users, {
    fields: [posts.userId],
    references: [users.id]
  });
  comments: many(comments);
});

export const comments = pgTable('comment', {
  id: serial('id').primaryKey(),
  comment: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  postId: integer('postId')
    .notNull()
    .references(() => posts.id),
  createdAt: timestamp('createdAt').defaultNow()
});

export const commentRelations = relations(comments, ({ one }) => {
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id]
  });
});

4 replies

Gaming

Programming