skip to Main Content

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

2

Answers


  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();
      }
    }
    
    Login or Signup to reply.
  2. This approach should maintain the integrity of your one-to-many relationship and ensure that OrderProduct entities are updated correctly when an Order is updated.

    async updateOrder(
      orderId: number,
      updateOrderPayload: UpdateOrderDto,
    ): Promise<Order> {
      let order = await this.getOrderById(orderId);
    
      if (!order) {
        throw new NotFoundException(`Order with id ${orderId} not found`);
      }
    
      await this.orderProductRepository.delete({ order: { id: orderId } });
      order = this.updateOrderRelatedFieldsOnly(order, updateOrderPayload) as Order;
    
      order.products = updateOrderPayload.products.map(productPayload => {
        const orderProduct = new OrderProduct();
        orderProduct.price = productPayload.price;
        orderProduct.quantity = productPayload.quantity;
        orderProduct.customizeName = productPayload.customizeName;
        orderProduct.color = productPayload.color;
        orderProduct.order = order;
        return orderProduct;
      });
     return await this.orderRepository.save(order);
    }
    

    In this code, orderProductRepository.delete removes all OrderProduct entities associated with the Order. The orderRepository.save call will persist the changes to the Order and due to the cascading options, it will also insert the new OrderProduct entities with the correct orderId.

    Please make sure that getOrderById includes the products relation if you are performing any logic that requires the current OrderProduct entities before they are removed.

    This approach should maintain the integrity of your one-to-many relationship and ensure that OrderProduct entities are updated correctly when an Order is updated.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search