likes
comments
collection
share

Sequelize:Error: Unknown column 'xxxxxx.id' in 'where clause'

作者站长头像
站长
· 阅读数 507

在使用Sequelize这个ORM框架开发服务端时,我们经常使用include来进行数据表的关联操作。

但是在嵌套关联表查询中使用Sequelize.literal进行子查询是会出现如下错误。

解决方法: 在上层添加separate属性。

   {
        model: DB.Answer,
        as: "answer_list",
        separate: true,
        include: [
          
        ],
      },

Sequelize:Error: Unknown column 'xxxxxx.id' in 'where clause'

目前这个错误只在嵌套查询的子查询中出现了,在普通的关联查询中并未发现。

Models.Answer.hasMany(Models.Likes, {
  foreignKey: "belong_id",
  sourceKey: "id",
  as: "like_data",
});

Sequelize:Error: Unknown column 'xxxxxx.id' in 'where clause'

属性对应文档

我对他的理解是:子查询不受顶层关联的影响,第三层关联和第一次关联的关系分离。

数据表:

Answer:

Sequelize:Error: Unknown column 'xxxxxx.id' in 'where clause'

Likes:

Sequelize:Error: Unknown column 'xxxxxx.id' in 'where clause'

这是精简后的查询,在查询问题数据时展示对应的答案以及答案的点赞情况。

查询:

await DB.Problem.findByPk(id, {
    include: [
         {
        model: DB.Answer,
        as: "answer_list",
        // separate: true,
        include: [
          {
            model: DB.Likes,
            as: "like_data",
            attributes: [
              [
                Sequelize.literal(
                  `(SELECT COUNT(id) FROM likes WHERE likes.belong_id = answer.id)`
                ),
                "like_count",
              ],
              [
                Sequelize.literal(
                  `(SELECT COUNT(id) FROM likes WHERE likes.user_id = ${ctx.id || -1})`
                ),
                "like_state",
              ],
            ],
          },
        ],
      },
    ],
  })