顺序关系表对齐错误

Question

我的项目中的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: [
// Sorted in the order in which likes were clicked    
[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"],        
// Sort the comments of the sorted posts in descending order.
[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
},

我几天来一直在设法解决这个问题，但找不到。

应该采取什么措施来解决上述问题？

Answer 1

您需要从顶部开始的所有关联。因为您有包含在Post中的Comment，所以您需要这样的东西。

order: [
[Sequelize.literal("`Liked->Like`.`createdAt`"), "DESC"],
[{model: Post, as:'Liked'}, Comment, 'createdAt', 'DESC']
]