One-to-many relationship records are not updating correctly in postgresql

Question

I have back-end server in nestjs with postgres and typeorm. The application is for small business for customize products in which admin can have products with basic info i.e name, description, cost, weight etc. In this business requirements, cost is fixed but price can vary to each individual order. So price is order specific. In this application, i have the following collections:

• Product(to store basic details of the product i.e cost, name, description, weight)
• Order (to store order details i.e order date, total amount, payment method etc)
• OrderProduct (to store order specific fields of product i.e customizeName, color, price)

Note: Inside OrderProduct i store the order specific fields i.e customizeName, color, price etc for each order

I have this entity of order

@Entity('orders')
  export class Order extends BaseEntity {
    @PrimaryGeneratedColumn('increment')
    id: number;

    @Column({
      length: 500,
    })
    description: string;

    @Column({
      nullable: true,
    })
    quantity: number;

    @Column({
      nullable: false,
    })
    amount: number;

    @Column({
      nullable: false,
    })
    paymentMethod: string;

    @ManyToOne(() => Customer, (customer) => customer.orders)
    @JoinColumn({ name: 'customerId' })
    @Index()
    customer: Customer;

    @OneToMany(() => OrderProduct, (orderProduct) => orderProduct.order, {
      cascade: ['insert', 'update', 'remove'],
    })
    @JoinTable({
      name: 'order-products',
      joinColumn: { name: 'orderId', referencedColumnName: 'id' },
      inverseJoinColumn: { name: 'productId', referencedColumnName: 'id' },
    })
    products: Array<OrderProduct>;

    @Column({
      nullable: true,
    })
    totalWeight: string;

    @Column({
      nullable: false,
      type: 'enum',
      enum: OrderStatus,
      default: OrderStatus.PENDING,
    })
    status: OrderStatus;

    @Column({
      nullable: false,
      default: new Date(),
    })
    orderDate: Date;

    @CreateDateColumn()
    createdAt: Date;
 }

I have this entity of OrderProduct

@Entity('order-products')
  export class OrderProduct extends BaseEntity {
    @PrimaryGeneratedColumn()
    id: number;

    @ManyToOne(() => Order, (order) => order.products, { onDelete: 'CASCADE' })
    @JoinColumn({ name: 'orderId' })
    order: Order;

    @ManyToOne(() => Product, (product) => product.orders)
    @JoinColumn()
    product: Product;

    @Column({
      nullable: false,
    })
    price: number;

    @Column({
      nullable: true,
    })
    quantity: number;

    @Column({ nullable: true })
    customizeName: string;

    @Column({ nullable: true })
    color: string;

    @CreateDateColumn()
    createdAt: Date;
}

I have this product entity

@Entity()
  export class Product extends BaseEntity {
    @PrimaryGeneratedColumn()
    id: number;

    @Index('productNameIdx')
    @Column({
      unique: true,
      length: 200,
      nullable: false,
    })
    name: string;

    @Column()
    description: string;

    @Column({
      nullable: false,
    })
    cost: number;

    @OneToMany(() => OrderProduct, (orderProduct) => orderProduct.product)
    orders: OrderProduct[];

    @Column()
    weight: string;

    @Column({
      nullable: true,
    })
    thumbnailImage: string;

    @CreateDateColumn()
    createdAt: Date;
}

Here is my service function to update order

  async updateOrder(
    orderId: number,
    updateOrderPayload: UpdateOrderDto,
  ): Promise<Order> {
   let order: Order = await this.getOrderById(orderId);

   if (!order) {
     throw new NotFoundException(`Order with id ${orderId} not    found`);
   }

   if (updateOrderPayload.products &&    updateOrderPayload.products.length > 0) {
     const detachedProducts = [...order.products];
     order.products = [];

     // Update order fields
     order = this.updateOrderRelatedFieldsOnly(
       order,
       updateOrderPayload,
     ) as Order;

     // Update order products
     if (
        updateOrderPayload.products &&
       updateOrderPayload.products.length > 0
     ) {
       for (const productPayload of updateOrderPayload.products) {
         const orderProduct = new OrderProduct();
         orderProduct.price = productPayload.price;
         orderProduct.quantity = productPayload.quantity;
         orderProduct.customizeName = productPayload.customizeName;
         orderProduct.color = productPayload.color;
         order.products.push(orderProduct);
       }
     }

     // Save the updated order
     const updatedOrder = await this.orderRepository.save(order);

     await this.orderProductRepository.remove(detachedProducts);

     return updatedOrder;
   } else {
     // If no products in the update payload, update order fields only
     order = this.updateOrderRelatedFieldsOnly(
       order,
       updateOrderPayload,
     ) as Order;

     // Save the updated order
     const updatedOrder = await this.orderRepository.save(order);

     return updatedOrder;
   }
 }

Note: While creating an order single product can be replicated with different special fields like single product "Water Bottle" can be included 3 times with different specific fiels like customizeName, color etc.

Now, the probelm i am facing is that when user creates an order let say 3 products so order creates successfully and correctly with 3 entries in order-products. Let say user selected water bottles 2 times with different customize name and color, one t-shirt with some customize name and color. On updating the order, order product entries are not updating correctly.

Expected Behaviour: But whenever user update any detail of the order i.e status or any other thing so previous order-product entries should be deleted and new entries of 3 products should be created with the same orderId and order should be updated correctly.

Current Behaviour: When user is updating the order then previous order product entries against that same order ID remains there and another 3 entries are creating in the order-products table without orderId

Answer 1

From your code:

// Save the updated order
const updatedOrder = await this.orderRepository.save(order);

await this.orderProductRepository.remove(detachedProducts);

When you update an order and attempt to update or replace its associated products, the orderId might not be getting properly assigned to the new OrderProduct instances, leading to orphaned entries in your order-products table.
Before adding new OrderProduct instances to the Order, make sure you correctly remove or update the existing ones. You might want to compare the incoming products with existing ones and decide whether to update existing entries or replace them entirely.

// Save the updated order
const updatedOrder = await this.orderRepository.save(order);

await this.orderProductRepository.remove(detachedProducts);

When you update an order and attempt to update or replace its associated products, the orderId might not be getting properly assigned to the new OrderProduct instances, leading to orphaned entries in your order-products table.
Before adding new OrderProduct instances to the Order, make sure you correctly remove or update the existing ones. You might want to compare the incoming products with existing ones and decide whether to update existing entries or replace them entirely.

async updateOrder(orderId: number, updateOrderPayload: UpdateOrderDto): Promise<Order> {
  // Start transaction
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    let order = await queryRunner.manager.findOne(Order, { where: { id: orderId }, relations: ['products'] });

    if (!order) {
      throw new NotFoundException(`Order with id ${orderId} not found`);
    }

    // If there are products in the update payload
    if (updateOrderPayload.products && updateOrderPayload.products.length > 0) {
      // Remove existing products related to the order
      await queryRunner.manager.remove(order.products);

      // Clear the products array to prepare for adding updated products
      order.products = [];

      // Iterate over incoming products and create new OrderProduct instances
      for (const productPayload of updateOrderPayload.products) {
        const orderProduct = new OrderProduct();
        orderProduct.order = order;
        orderProduct.price = productPayload.price;
        orderProduct.quantity = productPayload.quantity;
        orderProduct.customizeName = productPayload.customizeName;
        orderProduct.color = productPayload.color;
        // Add the new OrderProduct to the order's products array
        order.products.push(orderProduct);
      }
    }

    // Update other order fields from the payload as necessary
    // e.g., order.status = updateOrderPayload.status;

    // Save the updated order along with its products
    await queryRunner.manager.save(order);

    // Commit transaction
    await queryRunner.commitTransaction();

    return order;
  } catch (error) {
    // Rollback transaction on error
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    // Release transaction resources
    await queryRunner.release();
  }
}