Sorting Nulls and Empty Strings at Last in Sequelize Order By-CodePudding

I want to sort Nulls and Empty stings at the last in sequelize order by.

Here is the query I am able to run:

data = await Provider.findAll({
            where: {
              ...searchFilter,
            },
            order: [
              ['lastName', 'ASC NULLS LAST']
            ],
            ...({ limit: 40 }),
          });

I can sort Nulls at last, don't know how to add another condition in order by for the empty string. I want to sort Nulls and empty string at the last. I am using sequelize with postgress.

CodePudding user response：

you can put multiple conditions in order by like this

order: [
    ['lastName', 'ASC NULLS LAST'],
    [sequelize.literal('CASE WHEN lastName = '' THEN 0 ELSE 1 END'), 'DESC']
],

Let me know if this works or not

CodePudding user response：

Another option.

If you want to order null OR empty string at last without ordering null or empty string before another, you can use NULLIF.

order: [
    [sequelize.fn('NULLIF', sequelize.col('lastName'), ''), 'NULLS LAST']
]