CODEWITHSUNDEEP
postgresqlsequelize.js
Rahmat Ali
Rahmat Ali

Reputation: 1541

SequelizeJS: Wrong order of Column Values when using Numbers as String

I am new to SequelizeJS and using it for PostgreSQL with NodeJS application.

I have a table:

sequelize.define('log', {
    id: {
        type: type.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    statusCode: type.INTEGER,
    status: type.STRING,
    message: type.TEXT,
    lastRecordId: type.STRING,
    lastRecordTime: type.DATE
});

The problem is that, when I run a query for fetching the values from lastRecordId column in DESC order, I get wrong order of the values:

Wrong order of values

I did not want to use INTEGER nor BIGINT on that column, because it contains a code not a real number.

The query I am using is:

        LoggerModel
            .findAll({
                order: [ [ 'lastRecordId', 'DESC' ]],
            })
            .then( allLogs => {
                //...
            })

Upvotes: 3

Views: 3024

Answers (2)

Rahmat Ali
Rahmat Ali

Reputation: 1541

I have found a solution by using the answers of @bereket and from the answer here. What worked for me is as follows:

    LoggModel
        .findAll({
            order: [
                [ sequelize.cast(sequelize.col('lastRecordId'), 'BIGINT') , 'DESC' ]
            ]
        })
        .then((logs) => { /// })

Upvotes: 6

bereket gebredingle
bereket gebredingle

Reputation: 13016

You can cast it on the fly without changing the column to type. Then you can order on with that. 

LoggerModel
        .findAll({
            order: [
                     sequelize.cast('lastRecordId', 'BIGINT'),
                     [ 'lastRecordId', 'DESC' ]
              ],
        })
        .then( allLogs => {
            //...
        })

inspired by this

Upvotes: 2

Related Questions