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'],

(.html#ordering-and-grouping)

Does anyone know of an implementation to do this?

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/master/manual/model-querying-basics.html#ordering-and-grouping)

Does anyone know of an implementation to do this?

• javascript
• node.js
• sequelize.js

• "I cannot change this column into an Int." Why? What it actually means? – Jan Stránský Commented Jul 17, 2020 at 21:43
• Try to convert strings to numbers inside the sort function – Jan Stránský Commented Jul 17, 2020 at 21:44
• @JanStránský I cannot modify the table in any way, I must use it as is. – averageUsername123 Commented Jul 17, 2020 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 Commented Jul 17, 2020 at 21:50
• I have no experience with sequelize, sorry, just brainstorming. Can you create another column with ints? – Jan Stránský Commented Jul 17, 2020 at 21:54

2 Answers 2

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./p/5c23431b4e03

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

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.