How to use nested include in sequelize with where clause?

Question

I would like to fetch the results of specified college, Exam table has startDate, EndDate, noOfStudents and collage_id. Students table has examId & subject result table has studentId, score and grade. I have tried this below query it returns null for students and none for the exam also it has been returning all data which is present in result DB, I need only belongs to a specified college so any suggestion here, please

results.find({
   include: [{
      model: models.students,
      include: [{
         model: models.exam,
         as: 'exam',
         where:{collage_id:id}
      }],
      as: 'students'
   }]
});

Output:{
   "id": 1,
   "student_id":2,
   "score": 88,
   "grade" : B,
   "created_at": "2018-11-14T13:38:25.377Z",
   "updated_at": "2018-11-14T13:38:25.377Z",
   "students": null
}

Expected Output:{
   "id": 1,
   "student_id":2,
   "score": 88,
   "grade" : B,
   "created_at": "2018-11-14T13:38:25.377Z",
   "updated_at": "2018-11-14T13:38:25.377Z",
   "students": {
      "examId": 2
      "subject":
   },exam:{
      "startDate":Date, 
      "EndDate":date, 
      "noOfStudents" : 100
      "collage_id": 1
   }
}

score 0 · Accepted Answer · answered Nov 21 '18 at 05:56

I can see few confusions in your question.

According to your question in your Students table I can see you only have columns examId & subject. so how do you link Results table with Students table.

To link Results table with Students table your student table should be Students(id / studentId,examId,subject)

and the association (link) results.belongsTo(students,{foreignKey:'id / studentId'})

After doing these this only you can say,

results.find({
   include: [{
      model: models.students,
   }]
});

second problem I can see is you want an association (link) between your Students table and your Exams table, but your Exams table you only have columns startDate, EndDate, noOfStudents and collage_id. So where does the association here? How do you link student to exam.

I think the column noOfStudents means Number of students. If you mean the id of the student.

You have to have an association Student.hasMany(Exam,{foreignKey:'noOfStudents'})

After doing these to you will be able to query,

results.find({
   include: [{
      model: models.students,
      include: [{
         model: models.exam,
         where:{collage_id:id}
      }]
   }]
});

I'm not sure about the keyword as since I am also new to this little bit. as far as I know you have to use the keyword as in your associations before you use it your query though I'm not sure about it.

And also try to trace you query for this (console) and see what it generates. =)

How to use nested include in sequelize with where clause?

1 Answers1