Styjun

I'm developping an api with mongoose and nodejs.

Basically there is a Course Model, Student Model and Group Model. For every course there is two fields in course document:

. Field "students": array of objectids referencing Student Model.

. Field "groups": array of subdocuments, each subdocument has a "groupId" field referencing Group Model and a "students" field: an array of objectids referencing Student Model. These are the students enrolled in the group by course.

I can get the sorted student list by course, but I can't get this list by group. I can get the list unsorted, but not sorted.

These are my main schemas:

Group Model -> groups collection:

const groupSchema = new mongoose.Schema(
 name: 
 type: String,
 required: true,
 unique: true
 
)

Student Model -> students collection:

const studentSchema = new mongoose.Schema(
 name: 
 type: String,
 required: true,
 index:true
 ,
 firstName: 
 type: String,
 required: true,
 index:true
 ,
 lastName: 
 type: String,
 required: true,
 index:true
 
)

Course Model -> courses collection:

const courseSchema = new mongoose.Schema(
 name: 
 type: String,
 required: true,
 index: true
 ,
 students: [
 
 type: mongoose.Schema.Types.ObjectId,
 ref: 'Student'
 
 ],
 groups: [
 
 groupId: 
 type: mongoose.Schema.Types.ObjectId,
 ref: 'Group'
 ,
 students: [
 
 type: mongoose.Schema.Types.ObjectId,
 ref: 'Student' 
 
 ],
 
 ]
)

Using next query I get an alphabetically sorted student list by course, even I can filter with "where" without problems:

const query = await Course.findById(courseId)
 .select('students')
 .populate(
 path: 'students',
 select: ['_id', 'firstName', 'lastName', 'name'],
 match: where,
 options: 
 sort: 
 firstName: 1,
 lastName: 1,
 name: 1
 
 
 )

This query is fine,but I want another query to return the ordered student list by group and course. I'm using a similar query, but with a nested subdocuments array:

 const query = await Course.findById(courseId)
 .where('groups.groupId').equals(groupId)
 .select('groups.$.students')
 .populate(
 path: 'groups.students',
 select: ['_id', 'firstName', 'lastName', 'name'],
 match: where,
 // options: 
 // sort: 
 // firstName: 1,
 // lastName: 1,
 // name: 1
 // 
 // 
 )

This way I get the student list, but unsorted. If I uncomment the options to sort the populated list, I get the error:

"Cannot populate with sort on path groups.students because it is a subproperty of a document array".

I'm newbie in mongodb, before I worked with sql databases, but I'm learning mongodb with mongoose. I don't know if my schema design is the best to get what I want.

As this is not working, to fix the problem I apply the "sort" method to the query to sort, like this:

 query.groups[0].students.sort((st1, st2) => 
 if (st1.firstName > st2.firstName) return 1
 if (st1.lastName > st2.lastName) return 1
 if (st1.name > st2.name) return 1
 return -1
 )

Is there a way to get this using the query and/or populate methods?

Following the expert advices about mongodb aggregation framework I find out my first approach to solve my problem. The aggregate I've used:

db.courses.aggregate([
 $match: _id: ObjectId("5c6d43c98068bc0836a62b65") ,
 $project: 
 _id: 0,
 groups: '$groups'
 
 ,
 $unwind: "$groups" ,
 $match: 'groups.groupId': ObjectId("5c94b0d81ce16357d74549dd") ,
 $project: 
 students: '$groups.students'
 ,
 $unwind: '$students' ,
 $lookup: 
 from: "students",
 localField: "students",
 foreignField: "_id",
 as: "students"
 ,
 $project: 
 id: $arrayElemAt: [ "$students._id", 0 ] ,
 nie: $arrayElemAt: [ "$students.nie", 0 ] ,
 name: $arrayElemAt: [ "$students.name", 0 ] ,
 firstName: $arrayElemAt: [ "$students.firstName", 0 ] ,
 lastName: $arrayElemAt: [ "$students.lastName", 0 ] 
 ,
 $sort: firstName: 1, lastName: 1, name: 1 
])