Sequelize关联查询中评论排序触发ER_BAD_FIELD_ERROR的解决咨询

我的项目中Post和User表的关联关系如下：

// models/post.js
static associate(db) {
  db.Post.belongsTo(db.User);
  db.Post.belongsToMany(db.Hashtag, { through: 'PostHashtag' });
  db.Post.hasMany(db.Comment);
  db.Post.hasMany(db.Image);
  db.Post.belongsToMany(db.User, { through: 'Like', as: 'Likers' });    
}

// models/user.js
static associate(db) { 
  db.User.hasMany(db.Post);
  db.User.hasMany(db.Comment);
  db.User.belongsToMany(db.Post, { through: 'Like', as: 'Liked' })     
}

我通过Liked关联表按点赞时间排序帖子，以下查询能正常获取数据：

router.get('/liked', isLoggedIn, async (req, res, next) => { // loadLikedPostAPI / GET /user/liked
  try {
    const likedPosts = await User.findAll({   
      where: { id: req.user.id },      
      attributes: [],
      order: [
        // 按点赞点击时间排序    
        [Sequelize.literal("(`Liked->Like`.`createdAt`)"), "DESC"],     
      ],      
      include: [{
        model: Post,
        as: 'Liked',        
        include: [{
          model: User,
          attributes: ['id', 'nickname'],
        }, {
          model: User,
          as: 'Likers',
          attributes: ['id'],
        }, {
          model: Comment,          
          include: [{
            model: User,
            attributes: ['id', 'nickname'],
          }],
        }, {
          model: Image,
        }]
      }],
    });    

之后我想对已排序帖子的评论做降序排序，添加了以下排序规则：

order: [
  [Sequelize.literal("(`Liked->Like`.`createdAt`)"), "DESC"],        
  // 对已排序帖子的评论进行降序排序
  [Comment, 'createdAt', 'DESC'],
],   

但执行后报错：

{
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlState: '42S22',
  sqlMessage: "Unknown column 'Comments.createdAt' in 'order clause'",
  sql: 'SELECT `User`.`id`, `Liked`.`id` AS `Liked.id`, `Liked`.`title` AS `Liked.title`, `Liked`.`desc` AS `Liked.desc`, `Liked`.`ingredient` AS `Liked.ingredient`, `Liked`.`recipes` AS `Liked.recipes`, `Liked`.`tips` AS `Liked.tips`, `Liked`.`tags` AS `Liked.tags`, `Liked`.`createdAt` AS `Liked.createdAt`, `Liked`.`updatedAt` AS `Liked.updatedAt`, `Liked`.`UserId` AS `Liked.UserId`, `Liked->Like`.`createdAt` AS `Liked.Like.createdAt`, `Liked->Like`.`updatedAt` AS `Liked.Like.updatedAt`, `Liked->Like`.`PostId` AS `Liked.Like.PostId`, `Liked->Like`.`UserId` AS `Liked.Like.UserId`, `Liked->User`.`id` AS `Liked.User.id`, `Liked->User`.`nickname` AS `Liked.User.nickname`, `Liked->Likers`.`id` AS `Liked.Likers.id`, `Liked->Likers->Like`.`createdAt` AS `Liked.Likers.Like.createdAt`, `Liked->Likers->Like`.`updatedAt` AS `Liked.Likers.Like.updatedAt`, `Liked->Likers->Like`.`PostId` AS `Liked.Likers.Like.PostId`, `Liked->Likers->Like`.`UserId` AS `Liked.Likers.Like.UserId`, `Liked->Comments`.`id` AS `Liked.Comments.id`, `Liked->Comments`.`content` AS `Liked.Comments.content`, `Liked->Comments`.`createdAt` AS `Liked.Comments.createdAt`, `Liked->Comments`.`updatedAt` AS `Liked.Comments.updatedAt`, `Liked->Comments`.`UserId` AS `Liked.Comments.UserId`, `Liked->Comments`.`PostId` AS `Liked.Comments.PostId`, `Liked->Comments->User`.`id` AS `Liked.Comments.User.id`, `Liked->Comments->User`.`nickname` AS `Liked.Comments.User.nickname`, `Liked->Images`.`id` AS `Liked.Images.id`, `Liked->Images`.`src` AS `Liked.Images.src`, `Liked->Images`.`createdAt` AS `Liked.Images.createdAt`, `Liked->Images`.`updatedAt` AS `Liked.Images.updatedAt`, `Liked->Images`.`PostId` AS `Liked.Images.PostId` FROM `users` AS `User` LEFT OUTER JOIN ( `Like` AS `Liked->Like` INNER JOIN `posts` AS `Liked` ON `Liked`.`id` = `Liked->Like`.`PostId`) ON `User`.`id` = `Liked->Like`.`UserId` LEFT OUTER JOIN `users` AS `Liked->User` ON `Liked`.`UserId` = `Liked->User`.`id` LEFT OUTER JOIN ( `Like` AS `Liked->Likers->Like` INNER JOIN `users` AS `Liked->Likers` ON `Liked->Likers`.`id` = `Liked->Likers->Like`.`UserId`) ON `Liked`.`id` = `Liked->Likers->Like`.`PostId` LEFT OUTER JOIN `comments` AS `Liked->Comments` ON `Liked`.`id` = `Liked->Comments`.`PostId` LEFT OUTER JOIN `users` AS `Liked->Comments->User` ON `Liked->Comments`.`UserId` = `Liked->Comments->User`.`id` LEFT OUTER JOIN `images` AS `Liked->Images` ON `Liked`.`id` = `Liked->Images`.`PostId` WHERE `User`.`id` = 8 ORDER BY (`Liked->Like`.`createdAt`) DESC, `Comments`.`createdAt` DESC;',
  parameters: undefined
}

尝试多日无法解决，求解决办法。

问题核心是排序规则的层级错误：你在最外层User查询里直接排序Comment，但Comment是嵌套在Liked（Post）的include中的，Sequelize生成的SQL里评论表的实际别名是Liked->Comments，不是Comments，所以找不到对应字段。

有两种可行的解决方式：

方式一：在Comment的include配置内添加排序 #

直接在包含Comment的地方指定排序规则，确保每个帖子下的评论单独按创建时间降序排列，符合常规业务逻辑：

include: [{
  model: Post,
  as: 'Liked',        
  include: [{
    model: User,
    attributes: ['id', 'nickname'],
  }, {
    model: User,
    as: 'Likers',
    attributes: ['id'],
  }, {
    model: Comment,          
    include: [{
      model: User,
      attributes: ['id', 'nickname'],
    }],
    // 在这里添加评论的排序规则
    order: [['createdAt', 'DESC']]
  }, {
    model: Image,
  }]
}]

方式二：在外层order中使用正确的表别名 #

如果一定要在外层排序，需要用SQL里实际生成的别名，通过Sequelize.literal指定：

order: [
  [Sequelize.literal("(`Liked->Like`.`createdAt`)"), "DESC"],        
  // 使用正确的表别名
  [Sequelize.literal("(`Liked->Comments`.`createdAt`)"), "DESC"],
],   

注意：方式二会将所有评论和帖子全局排序，可能导致同一帖子的评论被分散，一般更推荐方式一。

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