Sequelize关联模型自定义CONCAT字段FullName在where子句识别失败如何解决

SQL 语句的执行顺序中，WHERE 子句优先级高于 SELECT 字段计算阶段，你在 attributes.include 中通过 sequelize.fn 定义的别名 FullName 是 SELECT 阶段的输出结果，WHERE 执行时还未生成该字段，因此会抛出未知字段错误。

• 方案1：WHERE 中直接复用 CONCAT 逻辑（最稳妥，性能最优）
  不用别名，直接在 WHERE 子句中写拼接逻辑，代码修改如下：

const { Op } = require('sequelize'); // 模糊搜索需要引入操作符

const salesEntities = await Sale.findAll({
  subQuery: false,
  include: [{
    model: Product,
    where: { RepresentativeId: representativeId },
  }, {
    model: Warranty,
    where: warrantyWhereStatements
  }, {
    model: Customer,
    attributes: {
      include: [[sequelize.fn("CONCAT", sequelize.col("Customer.FirstName"), sequelize.col("Customer.LastName")), "FullName"]],
    }
  }],
  order: orderOptions,
  where: [
    saleWhereStatements,
    // 直接在WHERE中写拼接逻辑
    sequelize.where(
      sequelize.fn("CONCAT", sequelize.col("Customer.FirstName"), sequelize.col("Customer.LastName")),
      search // 模糊搜索替换为：Op.like, `%${search}%`
    )
  ],
  limit: 4,
  offset: page * 4 - 4
});

• 方案2：使用 HAVING 子句筛选别名
  HAVING 执行顺序在 SELECT 之后，可以识别定义的别名，适合不想重复写拼接逻辑的场景：

const salesEntities = await Sale.findAll({
  subQuery: false,
  include: [{
    model: Product,
    where: { RepresentativeId: representativeId },
  }, {
    model: Warranty,
    where: warrantyWhereStatements
  }, {
    model: Customer,
    attributes: {
      include: [[sequelize.fn("CONCAT", sequelize.col("Customer.FirstName"), sequelize.col("Customer.LastName")), "FullName"]],
    }
  }],
  order: orderOptions,
  where: saleWhereStatements,
  // HAVING可以识别SELECT阶段定义的别名
  having: sequelize.where(sequelize.col('Customer.FullName'), search),
  limit: 4,
  offset: page * 4 - 4
});

• 方案3：在Customer模型中定义虚拟字段（复用性最高）
  如果多处需要用到FullName，直接在模型定义时配置虚拟字段，后续查询、筛选都可以直接用别名：

// Customer模型定义处新增虚拟字段配置
const Customer = sequelize.define('Customer', {
  FirstName: DataTypes.STRING,
  LastName: DataTypes.STRING,
  FullName: {
    type: DataTypes.VIRTUAL,
    // 配置查询时自动生成拼接逻辑
    include: [sequelize.fn("CONCAT", sequelize.col("FirstName"), sequelize.col("LastName")), "FullName"]
  }
}, {});

配置完成后你原本的$Customer.FullName$筛选写法就可以正常使用。

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