Sequelize操作PostgreSQL时products表code字段排序问题及解法
问题背景
我有一张products表,结构如下:
id | code | description --------------------------------------- 1 | 10 | description for product 10 2 | 2 | description for product 2 3 | 1 | description for product 1
对应的Sequelize模型定义如下:
module.exports = class Product extends Model { static init(sequelize) { return super.init({ id: { type: DataTypes.INTEGER, allowNull: false, unique: true, primaryKey: true }, code: { type: DataTypes.STRING, allowNull: false, unique: true }, description: { type: DataTypes.STRING, allowNull: true, unique: false } },{ sequelize, modelName: 'Product', tableName: 'products' }) } }
我尝试按code字段的字母数字顺序查询商品列表,但实际返回的是字典序排序结果:预期顺序为1 -> 2 -> 10,实际得到的顺序为1 -> 10 -> 2,我的查询代码如下:
function getAll(req, res, next) { models.Products.findAll({ where: {}, order: [ ['code', 'ASC'] ] }) .then(products => { res.send(products) }) }
我了解到PostgreSQL原生可以通过字段类型转换实现符合预期的排序:
SELECT code FROM products ORDER BY code::bytea;
但我不清楚在Sequelize中如何实现相同的效果。
已验证解决方案
我已通过如下方案解决了该问题:
function getAll(req, res, next) { models.Products.findAll({ attributes: [ 'id', 'code', [models.sequelize.literal(`case when code ~ '^[0-9]*$' then code::integer else null end`), 'sort_code'], 'description' ], where: {}, order: [ [models.sequelize.col('sort_code'), 'ASC'], ['code', 'ASC'] ] }) .then(products => { res.send(products) }) }
方案说明
- 首先通过
sequelize.literal拼接自定义SQL逻辑,对纯数字格式的code做整数类型转换,生成虚拟字段sort_code - 排序时优先按
sort_code升序排列,保证纯数字的编码可以按数值顺序排序,非数字编码再按原生code的字典序排列,兼容所有编码场景
内容的提问来源于stack exchange,提问作者agusgambina
相关产品推荐
相关产品推荐

