Sort By Virtual Column Not Working

[MrSharpp]

I have a virtual column, trayCount.

its being returned in the API response, but the sorting is not working.

I debugged the logs and got this query which was running:

SELECT `__root`.`id` AS `__root_id`, `__root`.`name` AS `__root_name`, `__root`.`construction_type` AS `__root_construction_type`, `__root`.`public_name` AS `__root_public_name`, `__root`.`rows` AS `__root_rows`, `__root`.`columns` AS `__root_columns`, (SELECT COUNT(*) FROM tray t WHERE t.pom_box_id = `__root`.id) AS `__root_tray_count` FROM `pom_box` `__root` ORDER BY "__root_trayCount" DESC LIMIT 10

I tried running this query in sql, it didnt worked. hence the sql itself is generated improperly.
And after spending some time i fixed the SQL

SELECT `__root`.`id` AS `__root_id`, `__root`.`name` AS `__root_name`, `__root`.`construction_type` AS `__root_construction_type`, `__root`.`public_name` AS `__root_public_name`, `__root`.`rows` AS `__root_rows`, `__root`.`columns` AS `__root_columns`, (SELECT COUNT(*) FROM tray t WHERE t.pom_box_id = `__root`.id) AS `__root_tray_count` FROM `pom_box` `__root` ORDER BY __root_tray_count DESC LIMIT 10

previously it was using __root_trayCount for ordering which is wrong, as well as it also included double quotes, but it shouldnt include it.

So here is the fix for query, is there any fix for this on nest js paginate code?

[MrSharpp]

solution:

Step1: go to nest-paginate and search for "${alias}", remove "" -> ${alias}
Step2: install patch-package, and run pnpm i (pnpm auto runs patch-package,if not using pnpm you need to run manually npx patch-package)

but there is an issue with this too:
if you are using SnakeNameStrategy in datasource for typeorm, it would give you error that o.virtualColumn doesnt exists,
because it transforms aliases into snake_case but not columnName hence the generated SQL is wrong.

thus the solution for it is to use snake case for virtual columns