Nuxt CNode：Node.js专业中文社区

请教大神， sequelize 非等值连接怎么实现？

发布于 4 年前作者 akaili 2507 次预览最后一次回复是 4 年前来自问答

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 回复

chenkai0520

1楼•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
    }
  }
})

akaili

2楼•4 年前

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

rrbe

3楼•4 年前

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

xiashulin

4楼•4 年前

你直接写个sql语句啊