Sequelize: Avoid duplicates when create with manyToMany associations

user_12488289 0
Viewed 497

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.

sequelize.js post orm sql
1 Answers

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.

user_3329880
Related