Sequelize PostgreSQL alphabetical sort

Question

I have a products table

id | code | description
---------------------------------------
1  | 10   | description for product 10
2  | 2    | description for product 2
3  | 1    | description for product 1

This is the sequelize model

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

I am trying to retrieve the products with a code alphanumeric order, but I get a lexicographic order, instead of 1 -> 2 -> 10 I get 1 -> 10 -> 2

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

I saw that in postgres I can cast the field to a

SELECT code
FROM products
ORDER BY code::bytea;

But I am not sure if it is possible to do the same with sequelize

UPDATE

I was able to fix with the selected answer

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

In SQL, you'd control the sort order with a CASE...END expression. Making some assumptions . . .

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

I don't use Sequelize any more, but if I get time, I'll try to translate that later.