Sequential relationship table alignment error-CodePudding

The post and user tables in my project form the relationship between the following tables.

// 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' })     
}

Using the Liked relationship table, the posts are sorted in the order they like them as follows.

Posts sorted in this way include a comment table.

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,
        }]
      }],
    });

As a result of running it, I was able to successfully get the data I wanted.

Afterwards, I wanted to sort the comments of the sorted posts in descending order, so I did an additional sort as follows.

order: [
  [Sequelize.literal("(`Liked->Like`.`createdAt`)"), "DESC"],        
  // Sort the comments of the sorted posts in descending order.
  [Comment, 'createdAt', 'DESC'],
],

However, the execution result was different from my expectations and resulted in the following error:

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
  },

I've been trying for days to fix the problem, but I can't find it.

What measures should be taken to solve the above problem?

CodePudding user response：

You need all associations from the top. Because you have Comment which is included within Post, you need something like this.

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