Order By two columns but have one order

This question is sequelize specific but I’d figure if I found a way to do in SQL then I could find a way to do it in sequelize

I need for the request to be ordered back by user.fullname which is joined by using include and by mockUser.fullname which is a JSON field.

One important thing to keep in mind however is that user is nullable in the case a mockUser is present.

This is what I have so far, it is ordering by mockUser.fullname and user.fullname, However, it is doing so separately, meaning there are two correct orders but they’re not ordered into one single order which is the desired result.

      const options = {
        where: args,
        include: ['user'],
        limit,
        offset,
        order: [['user', 'fullname', 'ASC'], ['mockUser.fullname', 'ASC']]
      }

SQL equivalent-ish of the above sequelize representation, it’s spit out by sequelize but I cleaned it up

Note: it turns every “user”.”column” as “user.column”

SELECT * FROM "table" 
LEFT OUTER JOIN "user" ON "table"."userId" = "user"."id" 
WHERE "table"."columnId" = '9efcbd5a-4dc7-4102-ad59-b6f6cd9adaa4'
ORDER BY "user"."fullname" ASC, ("table"."mockUser"#>>'{fullname}') ASC 
LIMIT 10 
OFFSET 0;

I’m using table and column to obfuscate the real names

Example output:

Adam Smith -> user
Beatrice Westfield -> user
John Smith -> user
Andy Brook -> mockUser
Mario Jimenez -> mockUser
Nadia Martinez -> mockUser

Desired Output:

Adam Smith -> user
Andy Brook -> mockUser
Beatrice Westfield -> user
John Smith -> user
Mario Jimenez -> mockUser
Nadia Martinez -> mockUser

As you can see it first sorts user and then mockUser, but how could I combine both?

Answer

I think you are looking for

order by coalesce (user, mockUser)

in this case if user is null it will order by mockuser.