Using CASE in orderBy leads to alias not found error

[jlefebvre1997]

Hello there 👋

I'm using nestjs-paginate for my project, and as far as it goes everything is perfect, except this one thing : I'm trying to sort my data by a specific column, but I want a specific value to always be placed first, for example :

.addOrderBy(
        `CASE 
           WHEN content.type = :folder THEN 1 
           WHEN content.type = :course THEN 2 
           WHEN content.type = :event THEN 3
           ELSE 3 
         END`,
        'ASC',
      )

However, this fails with this error : WHEN content" alias was not found. Maybe you forgot to join it?, even though I did provide the content alias when calling createQueryBuilder. Any idea on why this happens ?

Thanks for your time and help 🙏

[jlefebvre1997]

What I don't understand is that the generated SQL queries are absolutely identical (apart from the defaultSortBy that gets added at the end but even if I remove it it still fails)

[jlefebvre1997]

I fixed it by using addSelect instead of orderBy and ordering by the column I created :

        `CASE 
           WHEN content.type = :folder THEN 1 
           WHEN content.type = :course THEN 2 
           WHEN content.type = :event THEN 3
           ELSE 3 
         END`,
        'type_order',
      )
      .addOrderBy('type_order', 'ASC')

Seems strange that this work but orderingb by with Case directly doesn't