CODEWITHSUNDEEP
many-to-manysequelize.js
otong
otong

Reputation: 1545

Sequelize findOne-includes: conjunction table id is missing

I want to query a Subject with Student with the join table included. The relationship is many to many. I want to have the join table id (StudentSubject) but the returning result is empty

Subject.findOne({
            where:{id},
            include: [
                { model: Student, attribute: ['id', 'name']},
                { model: StudentSubject, attribute: ['id', 'subject', 'student', 'score'], as: "studentSubject"}
            ]
        })

The relationship is as follow

student

Student.belongsToMany(models.Subject, { through: models.StudentSubject, foreignKey: 'student'})
Student.belongsTo(models.StudentSubject, {foreignKey: 'id', targetKey: 'student', as: 'studentSubject'})

subject

Subject.belongsToMany(models.Student, { through: models.StudentSubject, foreignKey: 'subject'})
Subject.belongsTo(models.StudentSubject, { foreignKey: 'id', targetKey: 'subject', as: 'studentSubject'})

the join table models

// it has id by default in the migration file
const StudentSubject = sequelize.define('StudentSubject', {
    student: DataTypes.INTEGER,
    subject: DataTypes.INTEGER,
    score: DataTypes.INTEGER
  }

the result is missing the conjuntion table id, what do I miss here?

enter image description here

additional experiment

tried also with nested include in Student, doesn't work. it doesn't have the id too

Subject.findOne({
            where:{id},
            include: [
                { model: Student, attribute: ['id', 'name'], include: [
                    { model: StudentSubject, attribute: ['id', 'subject', 'student', 'score'], as: "studentSubject"}
                ]},
            ]
        })

Upvotes: 0

Views: 1517

Answers (2)

otong
otong

Reputation: 1545

turns out I have to define the id in the conjunction table models. I thought otherwise, so here it is

sequelize.define('StudentSubject', {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER
    },
    student: DataTypes.INTEGER,
    subject: DataTypes.INTEGER,
    score: DataTypes.INTEGER
  }

and I incorrectly define the associations, so I came to this

Student.hasMany(models.StudentSubject, {foreignKey: 'student'})
Subject.hasMany(models.StudentSubject, { foreignKey: 'subject'})
StudentSubject.belongsTo(models.Student, {foreignKey: 'student', sourceKey: 'id'})
StudentSubject.belongsTo(models.Subject, {foreignKey: 'subject', sourceKey: 'id'})

and now when querying with 

Subject.findOne({
            where:{id},
            include: [
                { model: Student, attribute: ['id', 'name'], include: [
                    { model: StudentSubject, attribute: ['id', 'subject', 'student', 'score'], as: "studentSubject"}
                ]},
            ]
        })

it now works

Upvotes: 1

Rastalamm
Rastalamm

Reputation: 1782

I haven't tested this out but you may need to include the id of the Subject table in your first code block. The below may get you what you are looking for. 

Subject.findOne({
        where:{id},
        include: [
            { model: Subject, attribute: ['id']},
            { model: Student, attribute: ['id', 'name']},
            { model: StudentSubject, attribute: ['id', 'subject', 'student', 'score'], as: "studentSubject"}
        ]
    })

Upvotes: 0

Related Questions