Reputation: 1787
I have three tables, and the structure is described as follows:
User table contains fields: id
, name
, date_created
Library table contains fields: id
, capacity
, date_created
, user_id
user_id is a foreign key for User table and each instance of Library must belong to a user.
The last table is Books table which contains fields: id
, status
, date_created
, library_id
.
library_id is a foreign key for Library table and each instance of a book must belong to a Library.
What I'm trying to achieve is a way to query the DB for all instances of a book that belongs to user and has a status of for example: borrowed
Upvotes: 1
Views: 11432
Reputation: 609
To implement joins first you need to establish associations between sequelize models. So, in your case
models.Library.hasMany(models.Books, {
foreignKey: 'library_id'
})
models.Books.belongsTo(models.Library, {
foreignKey: 'library_id'
})
models.Library.belongsTo(models.User, {
foreignKey: 'user_id'
})
models.User.hasMany(models.Library, {
foreignKey: 'user_id'
})
here, models
is a sequelize instance.
You can find more about sequelize associaitions here http: //docs.sequelizejs.com/manual/tutorial/associations.html
Now, To find all instances of Books that belongs to User
models.User.findAll({
include: [{
model: models.Library,
required: true, // do an INNER Join
include: [{
model: models.Books,
required: true,
where: {
status: 'borrowed'
}
}] t
}],
where: {
user_id: 'some_user_id'
}
})
Upvotes: 4
Reputation: 58613
I think include
is what you need , please check below query :
Books.findAll({
include : { //<------ By this you can use association
model : User ,
where : { id : YOUR_USER_ID }
},
where : { status : 'borrowed' }
}).then(books => {
if(books.length > 0) {
console.log(books);
} else {
console.log("No books found");
}
})
Upvotes: 1