sequelize联表查询如何通过JSON列某个属性排序？

下面代码应该怎么样通过Task表time进行排序, 下面的查询应该怎么写？

const { Sequelize, Model, DataTypes } = require('sequelize');
const sequelize = new Sequelize({
  dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
  database: 'test',
  host: 'localhost',
 	port: 3306,
  username: 'username',
  password: 'password',
  sync: { alter: true }
});

class Task extends Model {}
Task.init({
    title: Sequelize.STRING,
    data: Sequelize.JSON,
    uid: {
      type:Sequelize.STRING,
      primaryKey: true,
    }
  }, { sequelize, modelName: 'task_test' }
);
class User extends Model {}
User.init({
    username: Sequelize.STRING,
    uid: {
      type:Sequelize.STRING,
      primaryKey: true,
    }
  }, { sequelize, modelName: 'user_test' }
);

Task.sync({ alter: true })
User.sync({ alter: true })


User.hasMany(Task,{ foreignKey: 'uid', targetKey: 'uid' }); // 将uid添加到Task模型
Task.belongsTo(User, { foreignKey: 'uid', targetKey: 'uid' }); // 同样会将uid添加到Task模型中

// 假设Task的数据结构是
// {
//   title: 'test',
//   uid: '1'
//   data: { time: '1583265819366' }
// }

// 创建测试数据
 User.create({ username: 'jack', uid: '1'})
 User.create({ username: 'rose', uid: '2'})
 Task.create({ title: 'a', uid: '2', data: { time: '1583265819366' }})
 Task.create({ title: 'b',uid: '1', data: { time: '1583265819399' }})

// 应该怎么样通过Task表time进行排序, 下面的查询应该怎么写？
User.findAll({
  where: {
  },
  include: [Task],
  order: []
}).then((items) => {
  console.log(items)
})

Shonke 1楼•4 年前

需要过滤、排序、分组的字段请放在外面，或者留一份冗余，或者放在应用中进行排序非要这样用的话用 json_extract 和 Sequelize.literal 应该是可以做到

aov2005 2楼•4 年前

没记错的话好像是，具体要看看生成的sql

User.findAll({
  where: {
  },
  include: [
  {
  	model: Task,
	order: [["time","desc"]]
  }
  ],
  order: []
}).then((items) => {
  console.log(items)
})