I've been trying to apply unique index in combination in mongoDb databse using in my node.js backend, but unable to get away with the duplicate error

Question

The movie is supposed to be added to the single document named as watchlist of all user, but i tried to apply unique index in combination of profile_id(user) and content_id(movie_id) so that same movie can be added to the database for different users and different movies can be added for same user. But when im trying to add the same movie after logging in with different email, the duplicate error(error.code === 11000) is still being displayed.

const mongoose = require('mongoose')
const {ObjectId} = mongoose.Schema.Types

const WatchlistSchema = new mongoose.Schema({
    profile_id: { type: mongoose.Schema.Types.ObjectId, required: true, ref:'user'},
    content_id:{type:Number, required:true},
    mediaType:{type:String,required:true},
    title:{type:String, required:true}
})

WatchlistSchema.index({ profile_id: 1, content_id: 1 },{unique: true});

const WatchListModel = mongoose.model('watchlist',WatchlistSchema)

module.exports = WatchListModel

This is the Schema and Model

WatchlistRouter.post('/list',async(req, res)=>{
    const {list_id,media_type,name,ActiveUser} = req.body
    try {
        const user = await UserModel.findOne({email:ActiveUser})
        if(user){
            try {
                const entry = new WatchListModel({profile_id:user._id,content_id:list_id,mediaType:media_type,title:name})
                await WatchListModel.create(entry)
                res.status(200).send('Added to Watchlist successfully')
                
            } catch (error) {
                if (error.code === 11000) {
                    res.status(400).send(`This item is already in the watchlist for this ${ActiveUser }.`);
                } else {
                    res.status(500).send(`An error occurred: ${error.message}`);
                }
            }
        }else{
            res.status(400).send('You are not logged in to access this feature')
        }
        
    } catch (error) {
        res.status(500).send({message:error.message})
    }
})

This is the post operation for adding the movie

I do have an option to handle this issue in the controller by using relevant logic but i think it to be more appropriate if this is handled appropriate model design. I've tried to debug by consoling different parts and have searched the entire web but couldn't understand the reason.

Answer 1

I see that you are trying to create a collection where each document contains the user ID and the movie ID. The unique index on the combination of user_id and movie_id should prevent the same movie from being added multiple times for the same user but the issue might be that the index was not created correctly, which could cause it to check user_id and movie_id separately instead of checking it as a combination. Try to get all your indexes via getIndexes() command and check if it is correct. If it is correct and it still doesn't work, try dropping the index and recreate it.

But I suggest you to store the Movies in one collection and the users in other collection, and you add to the users collection a movies ID Array so you can store all the movies a user have added to his watchlist, this will make load times faster, and more efficient because if you get 10 movies that each user will add to the watchlist and you have 100 users you would have 1000 documents and MongoDB would have to search through all the documents to find the user one, and if you have it like this you will only have to go to the user id and retrieve each movie information by id and it will be faster to read.

const MovieSchema = new mongoose.Schema({
    movie_id: { type: Number, required: true },
    mediaType: { type: String, required: true },
    title: { type: String, required: true }
});

const WatchlistSchema = new mongoose.Schema({
    profile_id: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'user' },
    profileName: { type: String, required: true },
    movies: [{ type: Number }]
});

Also use

updateOne( { profile_id: userId }, 
           { $addToSet: { movies: movieId } } )

when adding a movie to an user if modifiedCount returns 0 means it doesn´t added it and if it returns 1 means it added it due the $addToSet Operator only adds the element to the array if it doesn´t exists already on that array also if matchedCount returns 0 means it haven't found any user with that id and if it returns 1 means it found an user with that id.