0

i am new to sequelize, like i just had to learn it today. I dont even know how to ask a question on it so please let me know how can i improve this. I have the below query.

router.get('/:id/work', async (req, res) => {
    let results = await User.findAll(
        {
            where: {
                id: req.params.id
            },
            include: [Task, Project]
        }
    );
    res.status(200);
    res.send(results);
});

I want to get the user info, and to have the 'tasks' and 'projects' as arrays of the 'assigner' and 'project_id' properties. Both my Project and Task model have these columns as foreign keys. The result I get is the one below


{
  id: 5,
  email: "[email protected]",
  name: "asdasdasd",
  surname: "asdasdasdasd1231",
Task: {
  id: 9,
  name: "req.body.name",
  description: "req.body.description",
  score: 1,
  status: "req.body.status",
  assigner: 5,
  project_id: null
},
Project: {
  id: 2,
  name: "req.body.name",
  body: "req.body.body",
  status: null,
  assigner: 5
 }
},
 {
  id: 5,
  email: "[email protected]",
  name: "asdasdasd",
  surname: "asdasdasdasd1231",
 Task: {
  id: 9,
  name: "req.body.name",
  description: "req.body.description",
  score: 1,
  status: "req.body.status",
  assigner: 5,
  project_id: null
 },
Project: {
  id: 3,
  name: "req.body.name",
  body: "req.body.body",
  status: "req.body.status",
  assigner: 5
 }
},

Is there any quick way to achieve this?

{
  id: 5,
  email: "[email protected]",
  name: "asdasdasd",
  surname: "asdasdasdasd1231",
  Task: [/*all task objects*/],
  Project: [/*all project objects*/]
}

Model definitions

const User = db.define('User', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    email: Sequelize.STRING,
    name: Sequelize.STRING,
    surname: Sequelize.STRING,
}, {
    timestamps: false,
    tableName: 'users'
})

/**
 * Model Relations
 */

Project.belongsTo(User, { foreignKey: 'assigner' })
User.hasOne(Project, { foreignKey: 'assigner' })

Task.belongsTo(User, { foreignKey: 'assigner' })
User.hasOne(Task, { foreignKey: 'assigner' })

Task.belongsTo(Project, { foreignKey: 'project_id' })

export default User;

const Task = db.define('Task', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    name: Sequelize.STRING,
    description: Sequelize.TEXT,
    score: Sequelize.INTEGER,
    status: Sequelize.STRING,
    assigner: {
        type: Sequelize.INTEGER,
        references: {
            model: User,
            key: 'id'
        }
    },
    project_id: {
        type: Sequelize.INTEGER,
        references: {
            model: Project,
            key: 'id'
        }
    }
}, {
    timestamps: false,
    tableName: 'tasks'
})


export default Task;
const Project = db.define('Project', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    name: Sequelize.STRING,
    body: Sequelize.STRING,
    status: Sequelize.STRING,
    assigner: {
        type: Sequelize.INTEGER,
        references: {
            model: User,
            key: 'id'
        }
    }
}, {
    timestamps: false,
    tableName: 'projects'
})

export default Project;
4
  • Can you describe the table relationship and how the above example is stored. Also how did you configured the mapping between User, Task and Project models. Commented Jun 3, 2020 at 17:53
  • @GhulamMohammedTaher i have made the relationship with I made it with Project.belongsTo(User), if i do it with hasMany(...) it throws an error that the user table does not have the 'assigner' column Commented Jun 3, 2020 at 18:29
  • show model definitions and associations Commented Jun 3, 2020 at 18:51
  • @Anatoly updated, thanks! Commented Jun 3, 2020 at 18:54

1 Answer 1

4

Change association definitions from hasOne to hasMany because as I see many projects can link to a one user as well as tasks:

User.hasMany(Project, { foreignKey: 'assigner' })
User.hasMany(Task, { foreignKey: 'assigner' })

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.