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?

"I cannot change this column into an Int." Why? What it actually means? — Jan Stránský, Jul 17 '20 at 21:43
@JanStránský I cannot modify the table in any way, I must use it as is. — averageUsername123, Jul 17 '20 at 21:49
@JanStránský I'm wondering how the sort function looks like, is it a simple sort(a, b) return either [-1,0,1] ? — averageUsername123, Jul 17 '20 at 21:50
I have no experience with sequelize, sorry, just brainstorming. Can you create another column with ints? — Jan Stránský, Jul 17 '20 at 21:54
Ciao, take a look at [this](https://stackoverflow.com/questions/36259532/sequelize-findall-sort-order-in-nodejs) answer. — Giovanni Esposito, Jul 17 '20 at 23:07

score 4 · Answer 1 · answered Jun 03 '21 at 07:21

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

score 1 · Answer 2 · answered Apr 27 '21 at 23:08

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 Answers2