Sequelize how to add in custom sort function?

Question

I have a table with a String column "grades" which includes the following ['uni', '9','10',11','12']. I cannot change this column into an Int.

I have the following sort code:

Course.findAll({
  order: [
    ['grade', 'ASC']
  ],
...

but sorting it by grade will give me the following order:

['10','11', '12', '9', 'uni' ]

Obviously I don't want it to be in this order. I looked into the Sequelize docs and they seem to have a way to submit a custom function for the ordering, but I don't have an example of how that would look like:

// Will order by  otherfunction(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

(https://sequelize.org/master/manual/model-querying-basics.html#ordering-and-grouping)

Does anyone know of an implementation to do this?

user_2537761 · Answer

You can use Sequalize.literal to set orderby value, please be careful that the value order is DESC so if you want grade 10 to show first, put it at the right end.

Course.findAll({
  order: [
    [Sequelize.literal("grade='uni', grade=9, grade=12, grade=11, grade=10")],
  ],
...

order by value ref: https://www.jianshu.com/p/5c23431b4e03

Sequalize.literal ref: https://sequelize.org/v5/class/lib/sequelize.js~Sequelize.html#static-method-literal

user_2579851 · Answer

The documentation is indeed a bit vague, I can only assume that by otherfunction they mean any other function supported by the dialect (mssql / mysql etc.) and the parameters it requires, but haven't tested this assumption yet.

However, I have found a workaround using sequelize.literal:

const customOrder = (column, values, direction) => {
  let orderByClause = 'CASE ';

  for (let index = 0; index < values.length; index++) {
    let value = values[index];

    if (typeof value === 'string') value = `'${value}'`;

    orderByClause += `WHEN ${column} = ${value} THEN '${index}' `;
  }

  orderByClause += `ELSE ${column} END`

  return [Sequelize.literal(orderByClause), direction]
};

Note that the index does not stay an int, but rather being treated as a string, and this is because the rest the values in this column are strings, and will be ordered as such when falling outside the CASE WHEN chain.

The way you'd use it would be:

Course.findAll({
  order: [
    customOrder('grade', ['uni', '9','10','11','12'], 'ASC')
  ],
...

This code takes an array of values (ordered in the desired order), and adds them to a CASE clause, giving every value it's respective index, which determines the order. so given the values above, you'll end up with:

  FROM
    ...
  WHERE
    ...
  ORDER BY CASE WHEN grade = 'uni' THEN '0'
                WHEN grade = '9' THEN '1'
                WHEN grade = '10' THEN '2'
                WHEN grade = '11' THEN '3'
                WHEN grade = '12' THEN '4'
                ELSE grade END ASC

As a reference, I used this blog post, and this issue ticket.

Sequelize how to add in custom sort function?

2 Answers