Compound Index With Subdocument

[Vincent Profeta]

I am looking to find a way to unique by multiple fields in a schema.

what I have read seems to lead me to using compound indexing. Basically what I am trying to do is unique a class (dance private lesson) by the date, the time.start, the instructor, which is a subdocument, and whether its cancelled. 

So if there is NOT already a class with the same instructor that starts at the same time on the same date thats not cancelled -- allow it. 

The schema is below. if someone could point me in the right direction that would be great.

	cancelled: {
	date: {
	type: Date,
	}
	},
	date: {
	type: Date,
	required: true
	},
	time: {
	start: {
	type: String,
	required: true
	},
	end: {
	type: String,
	required: true
	}
	},
	instructor: { type: mongoose.Schema.Types.ObjectId, ref: 'Users' },

[Wan Bachtiar]

what I have read seems to lead me to using compound indexing. … the instructor, which is a subdocument

Hi Vincent,

First of all, we need to clarify index on a sub-document.

As you can create indexes on embedded fields, just as you can index top-level fields in documents. For example, if you would like to index the name of the instructor from the document below:

{
    ...
    date: ISODate("2016-01-29T08:44:21.249Z"),
    instructor: {
        name: "Pierre Dulaine",
    }
}

You can create an index on embedded field name by:

db.collection.createIndex({"instructor.name":1})

This is not to be confused with creating indexes on embedded documents. Using the same document above, you can create an index on the document instructor by :

db.collection.createIndex({"instructor": 1})

 

instructor: { type: mongoose.Schema.Types.ObjectId, ref: ‘Users’ },

Looking at your mongoose schema, it does not appear that you are actually having a sub-document for instructor field. Mongoose will populate the field with the ObjectId from the Users model instead.

So the instructor field would actually be a top-level field. As an example, it may look like below:

{
    ...
    date: ISODate("2016-01-29T08:44:21.249Z"),
    instructor: ObjectId("56ab2665e4b6a5c926106ff5")
}

Although based on your mongoose schema, time.start and time.end are embedded fields. You can create indexes on embedded time start/end using indexes on embedded fields as discussed above. See also Create a Compound Index.

Basically what I am trying to do is unique a class (dance private lesson) by the date, the time.start, the instructor, which is a subdocument, and whether its cancelled.

You may also want to consider using embedded data model for instructor, instead of the current references data model. Both data models have its own merits depending on the use case, I would recommend reviewing and testing them both to see which one could benefit your application better. See Data Model Design for more info.

cancelled: { date: {type: Date,}},

I assumed that you are meaning to use the cancelled field as a status, if so you could utilise a boolean type instead of date. As if you are using the date and time of the cancellation, it could get quite complex with the unique index validation that you are trying to do.

Unless you are meaning to store the date and time of the cancellation for different purposes.

Best regards,

Wan.

​