请教大神， sequelize 非等值连接怎么实现？
sequelize 非等值连接怎么实现？
我现在有下面两个模型，要实现 SELECT e.salary, g.grade_level FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.lowest_sal; , 应该怎么做？
class employees extends Model {}
employees.init(
{
employee_id: {
type: DataTypes.INTEGER(6),
allowNull: false,
autoIncrement: true,
primaryKey: true
},
first_name: {
type: DataTypes.STRING(20)
},
last_name: {
type: DataTypes.STRING(25)
},
email: {
type: DataTypes.STRING(25)
},
phone_number: {
type: DataTypes.STRING(20)
},
salary: {
type: DataTypes.DOUBLE(10, 2)
},
commission_pct: {
type: DataTypes.DOUBLE(4, 2)
},
manager_id: {
type: DataTypes.INTEGER(6)
},
hiredate: {
type: DataTypes.DATE
}
},
{
sequelize: db,
modelName: "employees"
}
)
class job_grades extends Model {}
job_grades.init(
{
grade_level: {
type: DataTypes.STRING(3),
unique: true
},
lowest_sal: {
type: DataTypes.INTEGER(11)
},
highest_sal: {
type: DataTypes.INTEGER(11)
}
},
{
sequelize: db,
modelName: "job_grade"
}
)
job_grades.removeAttribute("id")
4 回复
https://sequelize.org/master/variable/index.html#static-variable-Op
Project.findAll({
where: {
id: {
[Op.and]: {a: 5}, // AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}], // (a = 5 OR a = 6)
[Op.gt]: 6, // id > 6
[Op.gte]: 6, // id >= 6
[Op.lt]: 10, // id < 10
[Op.lte]: 10, // id <= 10
[Op.ne]: 20, // id != 20
[Op.between]: [6, 10], // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[Op.in]: [1, 2], // IN [1, 2]
[Op.notIn]: [1, 2], // NOT IN [1, 2]
[Op.like]: '%hat', // LIKE '%hat'
[Op.notLike]: '%hat', // NOT LIKE '%hat'
[Op.iLike]: '%hat', // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat', // NOT ILIKE '%hat' (PG only)
[Op.overlap]: [1, 2], // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2], // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2], // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
},
status: {
[Op.not]: false // status NOT FALSE
}
}
})
