Sorting of the sequencing belongstomany relationship does not work-CodePudding

The post and user tables created through sequencing have a belongstomany relationship, and a mapping table called like is created.

db.Post.belongsToMany(db.User, { through: 'Like', as: 'Likers' });    
db.User.belongsToMany(db.Post, { through: 'Like', as: 'Liked' });

Using this, I wrote the following router to sort posts by the most likes.

const express = require('express');
const { Sequelize, Op } = require('sequelize');

const { Post, User, Image, Comment } = require('../models');

const router = express.Router();

router.get('/top', async (req, res, next) => { // loadTopPostsAPI / GET /posts/top
  try {
    const posts = await Post.findAll({
      limit: 20,
      offset: 0,                           
      // Sort posts by the most likes
      order: [[Sequelize.literal("(COUNT(`Likers->Like`.`PostId`))"), "ASC"]],      
      include: [{
        model: User, // Post author
        attributes: ['id', 'nickname'],
      }, {
        model: Image, // Post image
      }, {
        model: Comment, // Post Comment
        include: [{
          model: User, // Post Comment author
          attributes: ['id', 'nickname'],
        }],
      }, {
        model: User, // People who liked the post
        as: 'Likers',
        attributes: ['id'],               
      }],          
    })
    res.status(200).json(posts);
  } catch (error) {
    console.error(error);
    next(error);
  }
});

But when I run the router, I get the following error

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

How can I sort by resolving the above error?

CodePudding user response：

Sequelize tries to form Subquery by default with associations and ORDER BY clause is composed within the subquery. However, SQL's ORDER BY has to be at the top level, so many cases when you need ORDER BY, OFFSET, LIMIT, you need to disable the subquery by adding subQuery: false. This will make Sequelize to form the query with JOIN instead of subquery.

await Post.findAll({
    limit: 20,
    offset: 0,                           
    // Sort posts by the most likes
    order: [[Sequelize.literal("(COUNT(`Likers->Like`.`PostId`))"), "ASC"]],
    subQuery: false,
    ...
})

I bet this will make your current error go away but you have a new aggregation error, because this is trying to count full records which is disabled by MySQL by default. (ref: mysql error "ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query")

To fix this issue and do count Likes by Post id, add PARTITION BY.

await Post.findAll({
    limit: 20,
    offset: 0,                           
    // Sort posts by the most likes
    order: [[Sequelize.literal("COUNT(`Likers->Like`.`PostId`) OVER (PARTITION BY `Post`.`id`)"), "ASC"]],
    subQuery: false,
    ...
})