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

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.