Sequelize one to many query

sequelize.js

Hello I am trying to make a query between a one to many table (one user has many preferences). At the moment, I have a query that consist of a search for the ID of the user in the User table and a search in the preferences table for that ID. This is my working solution.

        const {username} = req.params

        User.findOne({where: {username: username}, attributes:['id']})
            .then(res => {
                const obj = res.get({plain:true})
                Preferences.findAll({ where:{ userId: obj.id}})
                    .then(res => {
                        const data = res.map((r) => (r.toJSON()));
                        console.log('sucess: ', data)
                    })
                    .catch(err => console.log('inner error'))
            .catch(err => console.log('outer error', err))
            })

How can I refine this with a .findAll query? I've come up with the following query which returns all the users with a foreign key in the Preferences table. However, I can't get this to work for a specific username.

        Preferences.findAll({
            include: [{
                model: User, 
                where: {userId: sequelize.where(Sequelize.col('userId'), Sequelize.col('User.id'))}
            }]
        })

If this question has already been answered can you kindly direct me to the post? I've been digging through Stack Overflow solutions but haven't had any luck. Thanks in advance!

//============================

Here's the final solution in case it helps anyone in the future. I fixed the association between my models and tweaked the query:

//User has many Preferences 
User.hasMany(models.Preferences, {
  foreignKey: 'userId', 
  sourceKey: 'id',
});

//Preferences has one User
Preferences.belongsTo(models.User,{
  foreignKey: 'userId', 
  targetKey: 'id',
  onDelete: 'CASCADE',
});


//Query for Preferences by Username

get: (req, res) => {
    const {username} = req.params
    User.findAll({
      include: [{
        model: Preferences, 
        }],
        where: {username: username}
    })
    .then(response => {
        const data = response.map((r) => (r.toJSON()));
        console.log('success: ', data[0].Preferences)   
    })
    .catch(err => console.log('outer error', err))
}

Best Answer

Make sure that you have declared the assocation between your models.

User.findAll({
  include: [{
    model: Preferences,
    where: { userId: Sequelize.col('user.id') }
  }]
})

sequlize documentation for creating assocation http://docs.sequelizejs.com/manual/tutorial/associations.html

Also here is a usefull blog link, https://medium.com/@THEozmic/how-to-create-many-to-many-relationship-using-sequelize-orm-postgres-on-express-677753a3edb5


If face any error, let me know through comments.

Related Topic