Custom sort typeorm

Question

I have a table called agreements which has a column named status. Values of status could either be "red", "yellow" or "green". I need to sort by red first, then yellow then green. My problem is that the query builder on typeorm automatically sorts it alphabetically which means that if I sort by the status column, the greens would come first then red then yellows.

Here is my code so far.

options.sort = 'agreement.status'

this.createQueryBuilder('agreement')
  .orderBy(options.sort)
  .limit(options.take)
  .offset(options.skip);

Answer 1

You can use case statements inside the query builder to accomplish this.

options.sort = 'agreement.status'

this.createQueryBuilder('agreement')
  .orderBy(`(case when ${options.sort} is "red" then 1 when ${options.sort} is "yellow" then 2 when ${options.sort} is "green" then 3 else null end)`)
  .limit(options.take)
  .offset(options.skip);

This will expose you to SQL injection if options.sort is user-provided, but I'm assuming it's not. More information on this here.