Sequelize使用$嵌套列$语法过滤嵌套字段时报错：缺失FROM子句表项

问题场景 #

在Express.js应用的通用控制器列表方法中添加anyField跨字段搜索逻辑，使用Sequelize处理数据库。路由传入的关联配置能正常生效，但嵌套字段搜索时触发数据库错误。

路由配置代码 #

generateAllCrudRoutes( apiAdminRouter, '/company_pilots', controllers.company_pilots, {
  needsPagination: true,
  includeModels: [ {
    model: Pilot,
    as: 'pilot',
    include: [ { model: User, as: 'user', duplicating: false } ]
  } ],
  anyFieldOptions: [
    '$pilot.user.firstName$',
    'pilot.user.lastName',
    'pilot.user.email',
    'pilot.user.mobile$',
    'pilot.user.passport_number',
    'pilot.user.sa_id_number'
  ],
} )

控制器搜索逻辑代码 #

if ( anyField && this.anyFieldOptions.length > 0 ) {
  queryOptions.where[ Op.or ] = this.anyFieldOptions.map( field => {
    const parts = field.split( '.' )

    if ( parts.length > 1 ) { 
      return {
        [ `$${ field }$` ]: { [ Op.iLike ]: `%${ anyField }%` }
      }
    } else {
      return {
        [ field ]: { [ Op.iLike ]: `%${ anyField }%` }
      }
    }
  } )
}

错误信息 #

error: missing FROM-clause entry for table "pilot->user"

生成的错误SQL语句 #

SELECT count(*) AS "count" FROM "CompanyPilots" AS "CompanyPilot" WHERE ("pilot->user"."firstName" ILIKE '%man%' OR "pilot->user"."lastName" ILIKE '%man%' OR "pilot->user"."email" ILIKE '%man%' OR "pilot->user"."mobile" ILIKE '%man%' OR "pilot->user"."passport_number" ILIKE '%man%' OR "pilot->user"."sa_id_number" ILIKE '%man%');

问题根源 #

1. 分页count查询未加载关联表：虽然includeModels配置了关联，但Sequelize的count方法默认不会自动包含关联表，导致WHERE子句引用嵌套字段时找不到对应表。
2. 字段格式不统一：anyFieldOptions中部分字段未用$包裹，导致Sequelize无法正确解析嵌套关联路径。

解决方案 #

1. 统一嵌套字段格式 #

所有嵌套关联字段都用$包裹，确保Sequelize正确识别关联路径：

anyFieldOptions: [
  '$pilot.user.firstName$',
  '$pilot.user.lastName$',
  '$pilot.user.email$',
  '$pilot.user.mobile$',
  '$pilot.user.passport_number$',
  '$pilot.user.sa_id_number$'
],

2. 强制count查询加载关联表 #

在分页逻辑中，给count查询手动传入include配置：

// 替换原count查询代码
const countOptions = {
  ...queryOptions,
  include: this.includeModels // 复用路由配置的关联模型
};
const total = await this.model.count(countOptions);

3. 简化字段处理逻辑 #

去掉多余的字段拆分判断，直接使用统一格式的字段：

if ( anyField && this.anyFieldOptions.length > 0 ) {
  queryOptions.where[ Op.or ] = this.anyFieldOptions.map( field => ({
    [field]: { [Op.iLike]: `%${anyField}%` }
  }));
}

验证效果 #

修改后生成的SQL会自动包含关联表的JOIN语句，示例如下：

SELECT count(*) AS "count" 
FROM "CompanyPilots" AS "CompanyPilot" 
LEFT OUTER JOIN "Pilots" AS "pilot" ON "CompanyPilot"."pilotId" = "pilot"."id" 
LEFT OUTER JOIN "Users" AS "pilot->user" ON "pilot"."userId" = "pilot->user"."id" 
WHERE ("pilot->user"."firstName" ILIKE '%man%' OR ...);

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