const { sequelize } = require('../../../models')
I defined employee model as
const sequelize = require('../utils/database')
module.exports = function (sequelize, DataTypes) {
const Employee = sequelize.define('Employee', {
empId: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
name: {
type: DataTypes.STRING,
field: 'name'
},
salary: {
type: DataTypes.FLOAT,
field: 'salary'
},
empDeptId: {
type: DataTypes.INTEGER,
field: 'empDeptId',
references: {
model: 'Dept',
key: 'deptId'
}
},
hiredate: {
type: DataTypes.DATE,
field: 'hiredate'
},
job: {
type: DataTypes.STRING,
field: 'job'
}
}, {
freezeTableName: true
});
return Employee;
}
and dept model as
const sequelize = require('../utils/database')
var Employee = require('./employee')
module.exports = function (sequelize, DataTypes) {
const Dept = sequelize.define('Dept', {
deptId: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
name: {
type: DataTypes.STRING,
field: 'name'
},
location: {
type: DataTypes.STRING,
field: 'location'
}
}, {
freezeTableName: true
});
return Dept;
}
Now Here are example of query using sequelize
1) Group By Having
var maxSalary = await db.Employee.findAll({
attributes: ['job', [db.sequelize.fn('max', db.sequelize.col('salary')), 'maxSalary']],
group: ["job"],
having: db.sequelize.where(
db.sequelize.fn('max', db.sequelize.col('salary')),
{
[db.Sequelize.Op.lte]: 6000,
}
)
})
2) Max without group by
var maxSalary = await db.Employee.findAll({
attributes: [
[db.sequelize.fn('max', db.sequelize.col('salary')), 'maxSalary']
]
})
3) inner join
var employees = await db.Employee.findAll({
where: { job: "MANAGER" },
attributes: ['name', 'salary', 'empDeptId', 'hiredate', 'job'],
include: [
{
model: db.Dept, as: 'Department',
required: false
}
],
limit: 5,
order: ["job"]
})
Here required is set false then Left Outer Join is created
if true then inner join created.
4) Between clause
var employees = await db.Employee.findAll({
where: {
hiredate: {
[db.Sequelize.Op.between]: ['1980-01-01 00:00:00', '2023-04-01 23:59:59']
}
},
attributes: ['name', 'salary', 'empDeptId', 'hiredate', 'job'],
include: [
{
model: db.Dept, as: 'Department',
required: false
}
],
limit: 5,
order: ["job"]
})
Code of this example can be found at https://github.com/gitsangramdesai/sequelize-validation
No comments:
Post a Comment