0

I come from this topic: NodeJS & Sequelize: How can I join 3 models?

I come to you now, because I need someone who can teach me about how to get started on junction models with NodeJS and Sequelize.

What I'm trying to do is, having 2 main models, for example, Employee and Office, a 3rd model called EmployeeOffice enters and connects the 2 main models, including its own field called "employee_chair". When i call a method, for example, Office.findAll, this is what I would get:

{
    "id": 1,
    "office_name": "Mars"
    "office_color": "Red",
    "createdAt": "2021-09-30T18:53:38.000Z",
    "updatedAt": "2021-09-30T18:53:38.000Z",
    "employees": [
        {
            "id": 1,
            "employee_name": "Albert",
            "employee_mail": "qalbert443@gmail.com",
            "createdAt": "2021-09-30T18:53:45.000Z",
            "updatedAt": "2021-09-30T18:53:45.000Z",
            "employee_office": {
                 "createdAt": "2021-09-30T18:53:45.000Z",
                 "updatedAt": "2021-09-30T18:53:45.000Z"
            }
        }
    ]
}

NOW. What I need, is to have the model called instead of the junction table that is automatically created. Because I can join the 3 tables, but the 3rd table has the field "employee_chair", that I mentioned earlier. The desired response would look like this:

{
    "id": 1,
    "office_name": "Mars"
    "office_color": "Red",
    "createdAt": "2021-09-30T18:53:38.000Z",
    "updatedAt": "2021-09-30T18:53:38.000Z",
    "employees": [
        {
            "id": 1,
            "employee_name": "Albert",
            "employee_mail": "qalbert443@gmail.com",
            "createdAt": "2021-09-30T18:53:45.000Z",
            "updatedAt": "2021-09-30T18:53:45.000Z",
            "employee_office": {
                 "employee_chair": 3,
                 "createdAt": "2021-09-30T18:53:45.000Z",
                 "updatedAt": "2021-09-30T18:53:45.000Z"
            }
        }
    ]
}

How can I do to make (or force) sequelize to make the relations through the model and not through the automatically created table?

Hope you guys can help me, I'm stuck and I don't know where to ask

@cupid22 Here is my index.js, userproject model and the controller function im calling:

index.js:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.projects = require("./project.model.js")(sequelize, Sequelize);
db.users = require("./user.model.js")(sequelize, Sequelize);

db.projects.belongsToMany(db.users, {
  through: "users_projects",
  as: "users",
  foreignKey: "user_id",
});

db.users.belongsToMany(db.projects, {
  through: "users_projects",
  as: "projects",
  foreignKey: "project_id",
});

module.exports = db;

Controller function:

    // Retrieve all Projects from the database.
exports.findAll = (req, res) => {
  const title = req.query.title;
  var condition = title ? { title: { [Op.like]: `%${title}%` } } : null;

  Project.findAll({ 
    include: [
    {
      model: User,
      as: "users",
    }]
  })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving projects."
      });
    });
};

UserProject Model:

    module.exports = (sequelize, Sequelize) => {
  const UserProject = sequelize.define('user_project', {
    user_id: {
      type: Sequelize.INTEGER,
      references: {
        model: 'user',
        key: 'id'
      }
    },
    project_id: {
      type: Sequelize.INTEGER,
      references: {
        model: 'project',
        key: 'id'
      }
    },
    user_type: {
      type: Sequelize.INTEGER,
    }
  }, {
    timestamps: false,
    tableName: 'users_projects'
  });

  db.users.belongsToMany(db.projects, { through: User_Profile });
  db.projects.belongsToMany(db.users, { through: User_Profile });

  return UserProject;
};
  • Look at https://sequelize.org/master/manual/advanced-many-to-many.html – Anatoly Oct 01 '21 at 17:46
  • I've been here for a week. In the documentation, it doesn't show where to write the relations. It says: User.belongsToMany(Profile, { through: Grant }); Profile.belongsToMany(User, { through: Grant }); . But, where do I write that? If I do in index.js, ERRROR. If I put it on the model, yet another error. I'm new to Node and it's being hard times – David Encina Martínez Oct 01 '21 at 18:24
  • You need to register all models first, and only then you need to register all associations. See my other answer here https://stackoverflow.com/a/61710568/1376618 – Anatoly Oct 01 '21 at 22:48
  • what is the sequelize code you are using in here add it as well. – cupid22 Oct 06 '21 at 08:38
  • Are you complete on migrations(do database have all the above mentioned tables)? If yes, then what I understand is that you have many to many association and now you want to fetch the data of the associations. Why do you not query the through the junction table and then just include the connected association\s I think that would be the ideal way to query such associations? Let me know if I am missing on something. – cupid22 Oct 11 '21 at 16:35

0 Answers0