search

ppetzold / nestjs-paginate

Pagination and filtering helper method for TypeORM repositories or query builders using Nest.js framework :book::paperclip:
MIT License
443 stars 99 forks source link

Using `CASE` in orderBy leads to `alias not found` error #1013

Open jlefebvre1997 opened 3 weeks ago

jlefebvre1997 commented 3 weeks ago

Hello there :wave:

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 :pray:

jlefebvre1997 commented 2 weeks ago

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 commented 2 weeks ago

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