Sequelize联表查询WHERE条件报错：未知列'apartment.townId'

错误原因分析 #

1. 关联表字段引用错误：req.query中携带的townId参数被直接合并到WHERE子句后，Sequelize默认将其识别为property表的字段，但实际上该字段属于关联的apartment表，未使用关联路径格式引用。
2. LIKE语句语法错误：代码中LIKE条件的通配符字符串未用反引号包裹，存在语法问题。
3. 关联模型别名不明确：关联查询未显式指定模型别名，可能导致字段引用路径不匹配。

修正步骤及完整代码 #

1. 处理req.query中的关联表字段 #

将req.query中属于关联表的字段转换为Sequelize支持的关联路径格式（$关联别名.字段名$）：

// 处理req.query中的关联表字段，比如townId属于apartment表
const processedQuery = {};
for (const key in req.query) {
  if (key === 'townId') {
    processedQuery['$apartment.townId$'] = req.query[key];
  } else {
    processedQuery[key] = req.query[key];
  }
}

2. 修正LIKE语句的语法 #

为通配符字符串添加反引号包裹，避免语法错误：

where[Op.or] = [
  { '$users.name$': { [Op.like]: `%${req.query.searchValue}%` } },
  { '$users.phone1$': { [Op.like]: `%${req.query.searchValue}%` } },
  { doorNo: { [Op.like]: `%${req.query.searchValue}%` } },
  { propertyNo: { [Op.like]: `%${req.query.searchValue}%` } },
];

3. 显式指定关联模型的别名 #

在include配置中明确指定as属性，确保字段引用路径的一致性：

conditions.include = [
  { model: req.db.user, as: 'users', required: false },
  { model: req.db.apartment, as: 'apartment', attributes: ['apartmentName'], required: false },
];

4. 合并处理后的查询条件 #

将处理后的req.query和自定义where条件合并：

conditions.where = { ...processedQuery, ...where };

完整修正代码 #

var conditions = {};
var where = {};

conditions.include = [
  { model: req.db.user, as: 'users', required: false },
  { model: req.db.apartment, as: 'apartment', attributes: ['apartmentName'], required: false },
];

if (req.query.searchValue) {
  where[Op.or] = [
    { '$users.name$': { [Op.like]: `%${req.query.searchValue}%` } },
    { '$users.phone1$': { [Op.like]: `%${req.query.searchValue}%` } },
    { doorNo: { [Op.like]: `%${req.query.searchValue}%` } },
    { propertyNo: { [Op.like]: `%${req.query.searchValue}%` } },
  ];
}

// 处理req.query中的关联表字段
const processedQuery = {};
for (const key in req.query) {
  if (key === 'townId') {
    processedQuery['$apartment.townId$'] = req.query[key];
  } else {
    processedQuery[key] = req.query[key];
  }
}

conditions.where = { ...processedQuery, ...where };
let properties = await req.db.property.findAll(conditions);

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