How to sort ENUM field order and set relation table condition to PostgreSQL with TypeORM?

Question

About Enum filed sort, if use MySQL, this way can sort ENUM field order:

ORDER BY FIELD(code, 'USD','CAD','AUD','BBD','EUR', 'GBP')

For PostgreSQL, maybe there isn't a built-in method, but this way works:

SELECT * FROM currency_codes
  ORDER BY
  CASE
    WHEN code='USD' THEN 1
    WHEN code='CAD' THEN 2
    WHEN code='AUD' THEN 3
    WHEN code='BBD' THEN 4
    WHEN code='EUR' THEN 5
    WHEN code='GBP' THEN 6
    ELSE 7
  END,name;

How to do it with TypeORM? I didn't find a field function there.

About set relation table condition to find method, I tried this way below(andWhere block)

const [items, total] = await this.findAndCount({
  where: {
    enable: true,
  },
  join: {
    alias: 'posts',
    innerJoinAndSelect: {
      category: 'posts.category',
    },
  },
  //andWhere: {
  //  'category.post_id': In(params.postId), // It doesn't work
  //},
  order: { CASE WHEN code='USD' THEN 1 ... }, // It doesn't work
});

Answer 1

In Postgres you can create a type enum, then define a column of that type and sort on the column.

The ordering of the values in an enum type is the order in which the values were listed when the type was created. All standard comparison operators and related aggregate functions are supported for enums.

So you can:

create type currency_type as enum ( 'USD','CAD','AUD','BBD','EUR', 'GBP'); 

create table payments( id            integer generated always as identity
                                             primary key
                     , cust_id       integer
                     , currency_code currency_type
                     ) ;

select * 
  from payments
 order by currency_code;

Further if the enum is altered to add new values the sorting automatically adjusts to the additions. (see demo)

Sorry but I do not know TypeORM so cannot provide that, but using the enum after creating it should not be difficult, creating it possibly.