Sequelize模型关联时如何在JOIN条件中使用函数做字段匹配

问题分析 #

Sequelize 标准的关联配置（hasOne/belongsTo等）默认仅支持直接的字段对等映射，无法直接在关联配置的foreignKey/sourceKey参数中直接传入原生函数表达式，你直接写字符串lower(name_department)会被Sequelize识别为普通的字段名或者字符串值，自然不会生效。

解决方法 #

方案1：查询时自定义JOIN的ON条件（最灵活，推荐） #

不需要修改原有模型关联的基础定义，在执行查询时通过include的on参数自定义关联条件，用Sequelize.fn调用数据库函数、Sequelize.col引用表字段即可，代码示例如下：

const employees = await Employee.findAll({
  include: [
    {
      model: Department,
      as: 'dpt',
      required: true, // 对应INNER JOIN，设为false就是LEFT JOIN
      on: Sequelize.where(
        Sequelize.col('emp.name_department'),
        '=',
        Sequelize.fn('lower', Sequelize.col('dpt.name_department'))
      )
    }
  ]
})

生成的SQL就和你需要的原生逻辑完全一致。

方案2：在关联定义中通过scope固化条件 #

如果你需要多次复用这个关联规则，也可以在定义关联时通过scope参数写入自定义条件：

Employee.hasOne(Department, {
  as: 'dpt',
  foreignKey: 'name_department',
  sourceKey: 'name_department',
  scope: Sequelize.where(
    Sequelize.col('dpt.name_department'),
    '=',
    Sequelize.fn('lower', Sequelize.col('emp.name_department'))
  )
})

后续直接正常调用关联查询即可，无需每次都写ON条件：

const employees = await Employee.findAll({
  include: ['dpt']
})

注意：该方案需要保证查询时表的别名和你在col中写的前缀一致，如果你修改了关联的as参数或者表别名，需要同步调整col里的字段前缀。

注意事项 #

所有原生函数、字段引用都必须通过Sequelize.fn和Sequelize.col封装，不要直接写SQL字符串，否则会被Sequelize自动转义为普通字符串值，无法生效。

内容的提问来源于stack exchange，提问作者Mr.P