I have a many to many relationship between User and Category through UserCategory as below.

let user = await User.findAll({
  where: {
    id: req.query.user
  },
  attributes: ["id", "name"],
  include: [
    {
      model: models.Category,
      as: "interests",
      attributes: ["id", "name", "nameTH", "icon"],
      through: {
        model: models.UserCategory,
        as: "user_categories",
        attributes: ["id", "userId", "categoryId", "updatedAt"]
      }
    }
  ],
  // Here, I want to order by updatedAt in user_categories
  order: [["user_categories", "updatedAt", "DESC"]] 
});

How can I order the result by "updatedAt" inside UserCategory model?

I have a many to many relationship between User and Category through UserCategory as below.

let user = await User.findAll({
  where: {
    id: req.query.user
  },
  attributes: ["id", "name"],
  include: [
    {
      model: models.Category,
      as: "interests",
      attributes: ["id", "name", "nameTH", "icon"],
      through: {
        model: models.UserCategory,
        as: "user_categories",
        attributes: ["id", "userId", "categoryId", "updatedAt"]
      }
    }
  ],
  // Here, I want to order by updatedAt in user_categories
  order: [["user_categories", "updatedAt", "DESC"]] 
});

How can I order the result by "updatedAt" inside UserCategory model?

• javascript
• sql
• node.js
• express
• sequelize.js

• What is your sequelize version? – Empty Brain Commented Feb 26, 2020 at 5:26
• "sequelize": "^5.21.3", "sequelize-cli": "^5.5.1" – theNu Commented Feb 26, 2020 at 5:55

3 Answers 3

Please refer the following code to sort the result by updatedAt inside UserCategory model-

let user = await User.findAll({
  where: {
    id: req.query.user
  },
  attributes: ["id", "name"],
  include: [
    {
      model: models.Category,
      as: "interests",
      attributes: ["id", "name", "nameTH", "icon"],
      through: {
        model: models.UserCategory,
        as: "user_categories",
        attributes: ["id", "userId", "categoryId", "updatedAt"]
      }
    }
  ],
  // Here, I want to order by updatedAt in user_categories
  order: [[Sequelize.literal('`interests->user_categories`.`updatedAt`'), 'DESC']] 
});

I hope it helps!

For those who have error like this:
ошибка синтаксиса (примерное положение: \".\") or syntax error (approximate position: \ ". \")

1. Go to Sequelize.literal() function

2. Change the argument string from single quotes ('') to double quotes ("")

3. Then, just swap backticks ( `` ) and double quotes ("")

let user = await User.findAll({
  where: {
    id: req.query.user
  },
  attributes: ["id", "name"],
  include: [
    {
      model: models.Category,
      as: "interests",
      attributes: ["id", "name", "nameTH", "icon"],
      through: {
        model: models.UserCategory,
        as: "user_categories",
        attributes: ["id", "userId", "categoryId", "updatedAt"]
      }
    }
  ],
  // Your changes here
  order: [[Sequelize.literal(`"interests->user_categories"."updatedAt"`), 'DESC']] 
  // order: [[Sequelize.literal('`interests->user_categories`.`updatedAt`'), 'DESC']] 
});

Special thanks to @SohamLawar

This solution is working for me with PostgreSQL. I'm able to add an order clause on the joining table.

let user = await User.findAll({
  where: {
    id: req.query.user
  },
  attributes: ["id", "name"],
  include: [
    {
      model: models.Category,
      as: "interests",
      attributes: ["id", "name", "nameTH", "icon"],
      through: {
        model: models.UserCategory,
        as: "user_categories",
        attributes: ["id", "userId", "categoryId", "updatedAt"]
      }
    }
  ],
  // Your changes here
  order: [[{model: models.Category, as: 'interests'}, {model: models.UserCategory, as: 'user_categories'}, 'updatedAt',  'DESC']] 
});