sequelize select attributes from the junction table

Question

I have been searching for a solution for hours now without hope.

I have Many-to-Many relationship with Student and Classes through a Junction Table Enrollment.

Everything is working fine and I am able to insert and retrieve data.

However, I don't want all the fields from the junction table to be returned in the result. Only selected attribute.

First, the below is my code:

Models definition and associations:

const Student = sequelize.define(
  "Student",
  { firstName: Sequelize.STRING },
  { timestamps: false }
);

const Class = sequelize.define(
  "Class",
  { className: Sequelize.STRING },
  { timestamps: false }
);

const Enrollment = sequelize.define(
  "Enrollment",
  {
    enrollmentType: {
      type: DataTypes.STRING,
    },
  },
  { timestamps: false }
);

Student.belongsToMany(Class, { through: Enrollment });
Class.belongsToMany(Student, { through: Enrollment });

The query:

return Student.findOne({
      where: { id: 2 },
      include: [
        {
          model: Class,
          attributes: ["className"],
        },
      ],
      raw: true,
      nest: true,
    });

And I got the below result:

{
  id: 2,
  firstName: 'John',
  Classes: {
    className: 'Chemistry',
    Enrollment: { enrollmentType: 'Normal student', StudentId: 2, ClassId: 1 }
  }
}

I am not interested in the repeated StudentId and ClassId returned from the Enrollment table.

Someone suggested that I use through:

return Student.findOne({
      where: { id: 2 },
      include: [
        {
          model: Class,
          attributes: ["className"],
          through: { attributes: ["enrollmentType"] },
        },
      ],
      raw: true,
      nest: true,
    });

But now I am getting the same result, only the order of fields seems to be different

{
  id: 2,
  firstName: 'John',
  Classes: {
    className: 'Chemistry',
    Enrollment: { ClassId: 1, StudentId: 2, enrollmentType: 'Normal student' }
  }
}

How can I only return the enrollmentType without other Enrollment fields?

You need to take out `raw: true` because this will disable some functionalities of model decompositions and `through: attributes` is one of them. — Emma, Oct 20 '22 at 21:47
Ok, I tried that and changed the return value to `data.toJSON()` but the results are showing `Enrollment: [Object]` . It is not expanding the Enrollment object in the return. I manually ran the query against the database and I can see that the results are correct and complete. Not sure why I am getting `Enrollment: [Object]` ... is there a way to expand this? — Yousi, Oct 21 '22 at 04:09
that is the nodejs's `console.log` limitation. If you want to see the value in terminal, either do `console.log(JSON.stringify(result.toJSON())` or `console.dir(result.toJSON(), {depth: null})` ref: https://stackoverflow.com/questions/10729276/how-can-i-get-the-full-object-in-node-jss-console-log-rather-than-object — Emma, Oct 21 '22 at 14:14
That's Emma, this is correct. I have used your comment as the answer to this question. Thank you for helping me out — Yousi, Oct 22 '22 at 15:41

score 0 · Accepted Answer · answered Oct 22 '22 at 15:41

I am using @Emma response in the comment as the solution to my issue.

This is absolutely correct. I created a simple ExpressJS API which returned the results accurately and fully.

As Emma mentioned, it is a console limitation.

See her full reply in the comments on the question.

sequelize select attributes from the junction table

1 Answers1