Issue with a ordering by @VirtualColumn

[celeron015]

Issue description

There is a issue in applying order by per a VirtualColumn

Expected Behavior

I'm expecting to have availability to order by a virtual column in the same way as the normal column.

Actual Behavior

In case I have a queryBuilder and I have a situation like this:

const queryBuilder = this.someRepository.createQueryBuilder('s');
queryBuilder.innerJoinAndSelect('s.relation', 'r');
queryBuilder.take(take);
queryBuilder.skip(skip);
queryBuilder.orderBy(**s.virtualColumn**, 'desc');

I cannot process sorting by s.virtualColumn because the SQL query uses an alias named
SELECT (query of my virtual column) as s_virtualColumn
Error that I'm getting:
Unknown column 's.virtualColumn' in 'order clause'

There also no way to process ordering by queryBuilder.orderBy(**s_virtualColumn**, 'desc');
Because typeORM wraps everything to a DISTINCT (something like this #4998)
Expression #1 of ORDER BY clause is not in SELECT list, references column 'distinctAlias.s_virtualColumn'

As I researched there is no that kind of problem when I'm not using take/skip but I need that because of joins and pagination.

It would be fine to treat a virtual column as a normal column so we can easily order by that since that is part of the object.

Steps to reproduce

Already described in the Actual Behaviour section.
My virtual column calculates some average values that I need to use for sorting.
I'm unable to order by a virtual column since the query is generated in this way:

queryBuilder.orderBy(s.virtualColumn, 'DESC');
'SELECT s.id AS s_id, s.column AS s_column, (query of my virtual column) as s_virtualColumn FROM .... ORDER BY s.virtualColumn DESC

My entity has a virtual column:
@VirtualColumn({
query: (alias) => some query where s.id = ${alias}.id
})
virtualColumn: number;

at the end of the query builder I'm expecting results by calling
await queryBuilder.getManyAndCount();

It's maybe also related to the #4742
@pleerock Any idea?

My Environment

Dependency	Version
Operating System	Ubuntu 20.04.5 LTS
Node.js version	v16.13.1
Typescript version	^4.9.5
TypeORM version	^0.3.16

Additional Context

No response

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[jpinxten]

Same bug on postgres

[kevin4dhd]

any solution?

[evgenovalov]

I also have an error when I try to sort by virtual column:

[Nest] 2841  - 09/29/2023, 3:22:13 PM   ERROR column ol.actualDelay does not exist

@VirtualColumn({
    type: "number",
    query: (alias) => `${alias}.date - ${alias}.initial_delivery_date`,
  })
  actualDelay: number | null;

All works fine until I want to sort by virtual column via { actualDelay: "DESC" }

[violette-m]

I have the same problem, it's working perfectly when you filter (using where) but not for ordering...
Mine is on Postgres

[v1n33th]

I believe the issue lies in not having a double quote around the alias(virtual column name) in the generated query while using order by.
see.