Sequelize: Avoid duplicates when create with manyToMany associations-CodePudding

I'm developing a small e-commerce to sell cinema tickets. I'm using Sequelize to design all models and their associations. The issue I'm facing is related with the route defined to create the final order:

Order.create({
        userId: req.body.userId,                   // Number
        sessionId: req.body.sessionId,             // Number
        seats: req.body.seats,                     // Array of objects
        offsiteProducts: req.body.offsiteProducts  // Array of objects
    },
    {
    include: [
        {
            model: Seat,
            attributes: ['area', 'number', 'roomId']
        },
        {
            model: OffsiteProduct,
            attributes: ['name', 'unitPrice']
        }
    ]
}).then(order => {
    res.json(order);
})

The relation between models is as follows:

User.hasMany(Order);
Order.belongsTo(User);

Session.hasMany(Order);
Order.belongsTo(Session);

Seat.belongsToMany(Order, { through: "reserved_seats" });
Order.belongsToMany(Seat, { through: "reserved_seats" });

OffsiteProduct.belongsToMany(Order, { through: ReservedOffsiteProduct });
Order.belongsToMany(OffsiteProduct, { through: ReservedOffsiteProduct });

For the "one-to-many" relationships, passing a foreign key is enough for Sequelize to associate models properly.

But for "many-to-many" associations ("belongsToMany" in Sequelize) it would duplicate the data entered for seats and offsite products and create it both as part of the order and as a new independent seat and offsite product respectively.

How can I avoid this behaviour and include the arrays of seats and offsite products only inside the final order? Thanks.

CodePudding user response：

One way to do this is to execute everything in a transaction. That way, it will be atomic, i.e. "all or nothing".

Sequelize provides some methods for many-to-many associations. In this case, a separate call could be made for each junction table.

With this in mind, the following should accomplish the inserts without any duplicates:

let t = await sequelize.transaction()

try { 
    let order = await Order.create({
                userId: req.body.userId,
                sessionId: req.body.sessionId
            }, {
                transaction: t
            })

    let seats = await Seat.findAll({
            where: {
                id: {
                    [Op.in]: req.body.seatIds
                }
            }
        })
    let offsiteProducts = await OffsiteProducts.findAll({
            where: {
                id: {
                    [Op.in]: req.body.offsiteProductIds
                }
            }
        })
    
    await order.addSeats(seats, { transaction: t })
    await order.addOffsiteProducts(offsiteProducts, { transaction: t })

    await t.commit()
} catch (err) {
    if (t) {
        await t.rollback()
    }
}

Alternatively, if the primary keys for the rows in the OffsiteProducts and Seats tables are already known, the above could be shortened to:

let t = await sequelize.transaction()

try { 
    let order = await Order.create({
                userId: req.body.userId,
                sessionId: req.body.sessionId
            }, {
                transaction: t
            })

    await order.addSeats(req.body.seatIds, { transaction: t })
    await order.addOffsiteProducts(req.body.offsiteProductIds, { transaction: t })

    await t.commit()
} catch (err) {
    if (t) {
        await t.rollback()
    }
}

There's a bit more explanation for passing arrays of primary keys to the .addSeats and .addOffsiteProducts methods in the docs here.