请教大神, sequelize 非等值连接怎么实现?
发布于 3 年前 作者 akaili 1790 次浏览 来自 问答

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
    }
  }
})

@chenkai0520 你好,这些操作符我知道怎么用 ,现在问题是 是做关联查询。但是两个表之间没有外键关联,我使用文档里边 里边的关联模型,也无法完成这个查询

@akaili 参考文档 你需要让 sequelize 知道这两个表之间用哪个 field 做关联,关联不一定要创建外键(设置 constraint: false)

你直接写个sql语句啊

回到顶部