Prisma•4mo ago

how do I do this upsert query in prisma?

INSERT INTO public."CartItem" ("itemId", "userId", "quantity", "ordered")
VALUES (${product.id}, ${user.id}, 1, false)
ON CONFLICT ("itemId", "userId") 
DO UPDATE SET 
  quantity = "CartItem".quantity + 1
RETURNING *;

INSERT INTO public."CartItem" ("itemId", "userId", "quantity", "ordered")
VALUES (${product.id}, ${user.id}, 1, false)
ON CONFLICT ("itemId", "userId") 
DO UPDATE SET 
  quantity = "CartItem".quantity + 1
RETURNING *;

basically I'm increasing the quantity by 1

5 Replies

RaphaelEtim•4mo ago

Hi @Snarkatude 👋 Can you please the schema file for this?

MozilOP•4mo ago

model CartItem {
  user      User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    BigInt
  Item      Item             @relation(fields: [itemId], references: [id])
  itemId    BigInt
  quantity  Int
  details   CartItemDetail[]
  createdAt DateTime         @default(now())
  ordered   Boolean
  // if order is TRUE, this must exist
  OrderItem OrderItem?

  @@id([userId, itemId])
}

model CartItem {
  user      User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    BigInt
  Item      Item             @relation(fields: [itemId], references: [id])
  itemId    BigInt
  quantity  Int
  details   CartItemDetail[]
  createdAt DateTime         @default(now())
  ordered   Boolean
  // if order is TRUE, this must exist
  OrderItem OrderItem?

  @@id([userId, itemId])
}

this is the cart item model

RaphaelEtim•4mo ago

Can you please try this query?

 const upsertCartItem = await prisma.cartItem.upsert({
  where: {
    userId_itemId: {
      itemId: item.id,
      userId: user.id,
    },
  },
  update: {
    quantity: {
      increment: 1,
    },
  },
  create: {
    itemId: item.id,
    userId: user.id,
    quantity: 1,
    ordered: false,
  },
  select: {
    itemId: true,
    userId: true,
    quantity: true,
    ordered: true,
  },
});

 const upsertCartItem = await prisma.cartItem.upsert({
  where: {
    userId_itemId: {
      itemId: item.id,
      userId: user.id,
    },
  },
  update: {
    quantity: {
      increment: 1,
    },
  },
  create: {
    itemId: item.id,
    userId: user.id,
    quantity: 1,
    ordered: false,
  },
  select: {
    itemId: true,
    userId: true,
    quantity: true,
    ordered: true,
  },
});

item.id and user.id are just placeholders

Dkyc•4mo ago

where: {
    userId_itemId: {
      itemId: item.id,
      userId: user.id,
    },
  },

where: {
    userId_itemId: {
      itemId: item.id,
      userId: user.id,
    },
  },

didn't know you could merge where clauses like that

RaphaelEtim•4mo ago

This part of the docs shows how it works https://www.prisma.io/docs/orm/prisma-client/special-fields-and-types/working-with-composite-ids-and-constraints#where-you-can-use-compound-ids-and-unique-constraints

Working with compound IDs and unique constraints (Concepts) | Prism...

How to read, write, and filter by compound IDs and unique constraints.

Gaming

Programming

how do I do this upsert query in prisma?