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

WaqarAli
December 8, 2022
181 views
2 votes
3 Answers

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: {
              search
            },
            order: [
              ['lastName', 'ASC NULLS LAST']
            ]
          });

Postgress by default sort the Null values at last, but for empty string it doesn’t take it as NULL rather it takes it as a string of length 0.
I want to sort the values with empty string at last, whether the empty string is at the first or at the last name it should be sorted at the end of list, giving preference to the names having both names.
I am using postgress with sequelize.

Update:
Following is the result I’m getting:

lastName	firstName
l_name1	f_name1
l_name2	empty_str
l_name3	f_name3
l_name4	f_name4
l_name5	f_name5

Following is the expected result:

lastName	firstName
l_name1	f_name1
l_name3	f_name3
l_name4	f_name4
l_name5	f_name5
l_name2	empty_str

For lastName is empty but firstName is not empty (expected result):

lastName	firstName
l_name1	f_name1
l_name4	f_name4
l_name5	f_name5
l_name2	empty_str
empty_str	f_name3

Answers

Chosen as BEST ANSWER
- WaqarAli
- December 13, 2022 at 9:26 am
- 0 votes
0
I was able to get the required result for my problem with the help of Emma's answer. Basically I wanted the sorting to be done on ‘last name’ basis and the people with missing ‘last_name’ or 'first_name' to be shown at the end of search list giving preference to people having both first and last names.

I was able to get the required result by adding Case statement in literal. For those not having knowledge of 'sequelize.literal', it is used to write raw SQL queries while using sequelize.

What the 'sequelize.literal' does it that it checks if either of the first or last name is equal to empty string, then it makes that row as NULL. Then the sorting is done on 'last_name' putting all the Null values at the last of list.
```
data = await Provider.findAll({
            where: {
              search
            },
            order: [
              [sequelize.literal(`CASE WHEN last_name = '' OR first_name = '' THEN NULL ELSE 0 END`), 'ASC'],
              ['lastName', 'ASC NULLS LAST']
            ],
          });
```

(Edit)

- DawoodFayyaz
- December 8, 2022 at 3:49 pm
- 0 votes
0
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
Login or Signup to reply.

- Emma
- December 8, 2022 at 8:28 pm
- 0 votes
0
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']
]
```
Update

Add firstName ordering.
```
order: [
    [sequelize.fn('NULLIF', sequelize.col('lastName'), ''), 'NULLS LAST'],
    [sequelize.fn('NULLIF', sequelize.col('firstName'), ''), 'NULLS LAST']
]
```
This will result in the order like this.

lastName firstName

Smith Alice

Smith Bob

Smith null

Smith

null Alice

Alice

null null

Update2

Order in the following criterion.
- Both lastName & firstName are not null and not empty
- Either lastName or firstName is not null or not empty
- Both lastName & firstName are null or empty
```
order: [
    [sequelize.literal(`
         CASE 
             WHEN NULLIF("lastName", '') IS NULL AND NULLIF("firstName", '') IS NULL THEN 2
             WHEN NULLIF("lastName", '') IS NULL OR NULLIF("firstName", '') IS NULL THEN 1
             ELSE 0
         END
    `)]
]
```
Login or Signup to reply.