CODEWITHSUNDEEP
javascripttypescriptdatabaseprismaplumatic-schema
nagynabil
nagynabil

Reputation: 11

create prisma many to many relation and one of the fields is from another table

I have a prisma schema with products, orders, and a many-to-many relation between them. I want to store the product price in the relation table because the product price can change at any time and when retrieving order data, I need the product price at the time of sale, not the new price. Is there a way to do this in Prisma without getting the products' prices first?

Here's the schema:

model Product {
  id          String            @id @default(cuid())
  name        String
  // todo add check stock can't be less than zero
  stock       Int
  // todo add check price can't be less than zero
  buyPrice    Float
  sellPrice   Float
  image       String
  createdAt   DateTime          @default(now())
  createdBy   User              @relation(fields: [createdById], references: [id])
  createdById String
  category    Category          @relation(fields: [categoryId], references: [id])
  categoryId  String
  orders      ProductsOnOrder[]
}

model Order {
  id          String            @id @default(cuid())
  // guess it's suppose to be computed value
  total       Float
  paymentType PaymentType       @default(CASH)
  createdAt   DateTime          @default(now())
  createdById String
  createdBy   User              @relation(fields: [createdById], references: [id])
  products    ProductsOnOrder[]
}

model ProductsOnOrder {
  productId String
  Product   Product @relation(fields: [productId], references: [id])
  orderId   String
  order     Order   @relation(fields: [orderId], references: [id])
  quantity  Int     @default(1)
  price     Float // Price of the product at the time of the order

  @@id([productId, orderId])
}

Upvotes: 1

Views: 682

Answers (1)

Ali
Ali

Reputation: 1

When you create an order in your application code, you will need to manually fetch the current price of the product and include it in the creation of the ProductsOnOrder record.

const createOrderWithProducts = async(userId, orderDetails) => {
  const {
    paymentType,
    products
  } = orderDetails; // products is an array of { productId, quantity }

  return prisma.$transaction(
    products.map(async(product) => {
      const fetchedProduct = await prisma.product.findUnique({
        where: {
          id: product.productId
        },
      });

      return prisma.productsOnOrder.create({
        data: {
          quantity: product.quantity,
          price: fetchedProduct.sellPrice,
          Product: {
            connect: {
              id: product.productId
            },
          },
          Order: {
            create: {
              total: fetchedProduct.sellPrice * product.quantity, // this is an oversimplification, you would need to calculate the total price of all products
              paymentType,
              createdBy: {
                connect: {
                  id: userId
                },
              },
            },
          },
        },
      });
    })
  );
};

Upvotes: 0

Related Questions