2

I have 4 models: User, Skill, SkillToLearn, and SkillToTeach. The SkillToLearn and SkillToTeach contain two columns userId and skillId to keep track of a skill (given by skillId) that a user (given by userId) wants to learn or teach.

My goal is to use Sequelize's include statement such that I can return all users' data, including a list of skills they are learning and a list of skills they are teaching. I want a response similar to the following:

[
    {
        "id": 1,
        "username": "janedoe",
        "skillsLearning": [
            {
                "skillId": 1,
                "name": "arts"
            }
        ],
        "skillsTeaching": [
            {
                "skillId": 2,
                "name": "cooking"
            }
        ]
    }
]

However, instead, I'm getting the following response:

[
    {
        "id": 1,
        "username": "janedoe",
        "skillsLearning": [
            {
                "skillId": 1,
                "Skill": {
                    "name": "arts"
                }
            }
        ],
        "skillsTeaching": [
            {
                "skillId": 2,
                "Skill": {
                    "name": "cooking"
                }
            }
        ]
    }
]

I have tried to include Skill instead of SkillToLearn and SkillToTeach but I got an error saying that Skill is not associated to User. I am uncertain if my schemas and associations are incorrect.

User.js

    const User = sequelize.define("User", {
        username: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true,
            validate: {
                isEmail: true
            }
        },
        password: {
            type: DataTypes.STRING,
            allowNull: false
        },
        description: {
            type: DataTypes.TEXT
        }
    }, {
        freezeTableName: true
    });

    User.associate = function (models) {
        User.hasMany(models.SkillToLearn, {
            as: "skillsLearning",
            onDelete: "cascade",
            foreignKey: "userId",
            sourceKey: "id"
        });

        User.hasMany(models.SkillToTeach, {
            as: "skillsTeaching",
            onDelete: "cascade",
            foreignKey: "userId",
            sourceKey: "id"
        });
    };

Skill.js

const Skill = sequelize.define("Skill", {
        name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        }
    }, {
        freezeTableName: true
    });

    Skill.associate = function (models) {
        Skill.hasMany(models.SkillToLearn, {
            as: "usersLearning",
            onDelete: "cascade",
            foreignKey: "skillId",
            sourceKey: "id"
        });

        Skill.hasMany(models.SkillToTeach, {
            as: "usersTeaching",
            onDelete: "cascade",
            foreignKey: "skillId",
            sourceKey: "id"
        });
    };

SkillToLearn.js

const SkillToLearn = sequelize.define("SkillToLearn", {
        userId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        skillId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        }
    }, {
        freezeTableName: true
    });

    SkillToLearn.associate = function (models) {
        SkillToLearn.belongsTo(models.User, {
            foreignKey: "userId",
            targetKey: "id"
        });

        SkillToLearn.belongsTo(models.Skill, {
            foreignKey: "skillId",
            targetKey: "id"
        });
    };

SkillToTeach.js

const SkillToTeach = sequelize.define("SkillToTeach", {
        userId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        skillId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        }
    }, {
        freezeTableName: true
    });

    SkillToTeach.associate = function (models) {
        SkillToTeach.belongsTo(models.User, {
            foreignKey: "userId",
            targetKey: "id"
        });

        SkillToTeach.belongsTo(models.Skill, {
            foreignKey: "skillId",
            targetKey: "id"
        });
    };

dataRoutes.js

db.User
        .findAll({
            attributes: ["id", "username"],
            include: [
                {
                    model: db.SkillToLearn,
                    as: "skillsLearning",
                    attributes: ["skillId"],
                    include: [
                        {
                            model: db.Skill,
                            attributes: ["name"]
                        }
                    ]
                },
                {
                    model: db.SkillToTeach,
                    as: "skillsTeaching",
                    attributes: ["skillId"],
                    include: [
                        {
                            model: db.Skill,
                            attributes: ["name"]
                        }
                    ]
                }
            ]
        })
        .then(results => res.json(results));
});

Is there a way for me to get the skill's name without having it in an object? Should I just do multiple queries and construct the response using my own object literal? Thank you!

Truc Ly Le
  • 21
  • 2
  • 1
    I think the problem is you're turning a many-to-many relation into two one-to-many relations. You need to define a relation like `User.belongsToMany(Skill, { through: SkillToLearn, as: 'skillsToLearn' })` More info [here](http://docs.sequelizejs.com/manual/tutorial/associations.html#belongs-to-many-associations). – Ankh Feb 11 '19 at 08:36

0 Answers0