Order by nickName if nickName is not null, otherwise order by firstName

Question

I have the same problem asked here

But I didn't find any solution to do this with nestJs and postgres with Repository-orm.

this is my code.

  const contacts = await this.contactRepository.find({
  order: { nickName: 'ASC', firstName: 'ASC', lastName: 'ASC' },
});

Answer 1

You have to use QueryBuilder. See this answer for more information.

As you correctly suggested in this, you have to use the ORDER BY clause in combination with COALESCE(...).

Example query:

const contacts = await this.contactRepository
  .createQueryBuilder('contact')
  .orderBy('COALESCE(contact.nickName, contact.firstName, contact.lastName)', 'ASC')
  .getMany();