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.
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.