如何在Sequelize的findAll中结合T-SQL的COLLATE与LIKE使用？

问题描述 #

我在Sequelize中有如下查询脚本：

findAll(People, {
  where: {
    [Op.or]: [
      { firstName: { [Op.like]: `%${search}%` } },
      { lastName:  { [Op.like]: `%${search}%` } },
    ]
  }
});

该脚本会生成如下WHERE子句：

WHERE
  (
    [Player].[firstName] LIKE N'%test%'
    OR [Player].[lastName] LIKE N'%test%'
  )

我希望添加COLLATE Latin1_general_CI_AI来实现不区分重音的搜索，最终期望生成的SQL如下：

WHERE
  (
    [Player].[firstName] COLLATE Latin1_general_CI_AI LIKE N'%test%'
    OR [Player].[lastName] COLLATE Latin1_general_CI_AI LIKE N'%test%'
  )

请问如何通过Sequelize实现这一需求？

解决方案 #

方法一：使用Sequelize的fn和col方法 #

通过fn调用LIKE函数并指定COLLATE规则，同时用col指代字段，避免硬编码表名：

const { Op, fn, col } = require('sequelize');

findAll(People, {
  where: {
    [Op.or]: [
      fn('LIKE', col('firstName'), `%${search}%`, 'COLLATE Latin1_general_CI_AI'),
      fn('LIKE', col('lastName'), `%${search}%`, 'COLLATE Latin1_general_CI_AI')
    ]
  }
});

方法二：使用literal构建查询表达式 #

直接编写包含COLLATE的SQL片段，注意要防范SQL注入风险，优先使用参数绑定：

const { Op, literal } = require('sequelize');

findAll(People, {
  where: literal(`
    [Player].[firstName] COLLATE Latin1_general_CI_AI LIKE :search 
    OR [Player].[lastName] COLLATE Latin1_general_CI_AI LIKE :search
  `),
  replacements: { search: `%${search}%` }
});

注意：禁止直接将用户输入的search参数拼接进literal的字符串中，否则会引发SQL注入风险，必须使用参数绑定方式处理。

内容的提问来源于stack exchange，提问作者adamsfamily