In Sequalize we can define foreign key relation between entities.That is latter used to fetch related entity in single query.On the backend those queries translate into join.
We have following entities,please refer entity diagram below
There is one to one relationship between course & coursedetails,one to many relationship between course & tutorials & Many to Many relationship between Tag & Tutorials through third table tutorialTags.
This is defined with sequelize as follows
/*one to one*/
db.courses.hasOne(db.courseDetail,{
as: "courseDetail",
foreignKey: "courseId",
});
/*many to many*/
db.tutorials.belongsToMany(db.tags, {
through: "tutorialTag",
as: "tags",
foreignKey: "tutorialId",
});
db.tags.belongsToMany(db.tutorials, {
through: "tutorialTag",
as: "tutorials",
foreignKey: "tagId",
});
/*one to many*/
db.courses.hasMany(db.tutorials, { as: "tutorials" });
db.tutorials.belongsTo(db.courses, {
foreignKey: "courseId",
as: "course",
});
so while fetching all tags we can include tutorials with it as
exports.findAll = (req, res) => {
Tag.findAll({
where: {} ,
include: [{
model: Tutorial, as: "tutorials",
attributes:['title', 'description']
}]
})
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving tutorials."
});
});
};
we can fetch coursedetails & tutorials while fetching course
exports.findAll = (req, res) => {
const name = req.query.name;
var condition = name ? { name: { [Op.like]: `%${name}%` } } : null;
Course.findAll(
{
where: condition ,
include: [{
model: Tutorial, as: "tutorials",
attributes:['title', 'description']
},
{
model: CourseDetail, as: "courseDetail",
attributes:['instructor', 'prerequisite']
}
]
})
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving courses."
});
});
};
While fetching tutorial we can fetch course & tag
exports.findAll = (req, res) => {
const title = req.query.title;
var condition = title ? { title: { [Op.like]: `%${title}%` } } : null;
Tutorial.findAll({
where: condition ,
include: [{
model: Course, as: "course",
attributes:['name', 'description']
},
{
model: Tag,as: "tags",
attributes: ['name']
}]
})
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving tutorials."
});
});
};
Sequalize let us fetch foreign key data in single query as demonstrated above.
Code for tutorial can be viewed at https://github.com/gitsangramdesai/express-sequelize-mysql-node.
No comments:
Post a Comment