postgresql - Sequelize PostgreSQL 字母排序

Question

我有一个产品表

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

我正在尝试使用代码字母数字顺序检索产品，但我得到的是字典顺序，而不是 1 -> 2 -> 10 我得到的是 1 -> 10 -> 2

function getAll(req, res, next) {
  models.Products.findAll({
    where: {},
    order: [
        ['code', 'ASC']
    ]
  })
  .then(products => {
    res.send(products)
  })
}

我看到在 postgres 中我可以将字段转换为

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

Answer 1

在 SQL 中，您可以使用 CASE...END 表达式控制排序顺序。做一些假设。 . .

create table products (
    id integer primary key,
    code varchar(5) not null unique,
    description varchar(30) null
);


insert into products values
(1, 10,     'description for product 10'),
(2, 2,      'description for product 2'),
(3, 1,      'description for product 1'),
(4, 'A10',  'description for product A10'),
(5, 20,     'description for product 20'),
(6, 99,     'description for product 99'),
(7, 21,     'description for product 21'),
(8, 'RA10', 'description for product R10');


select id, 
       code, 
       case when code ~ '^\d*$' then code::integer else null end as sort_code, 
       description
from products
order by sort_code, code;


id  code  sort_code  description
--
3   1     1          description for product 1
2   2     2          description for product 2
1   10    10         description for product 10
5   20    20         description for product 20
7   21    21         description for product 21
6   99    99         description for product 99
4   A10              description for product A10
8   RA10             description for product RA10

我不再使用 Sequelize，但如果我有时间，我会稍后尝试翻译。