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?
Advertisement
Answer
I think you are looking for
order by coalesce (user, mockUser)
in this case if user is null it will order by mockuser.