Column missing in order clause if pagination is active and the column is the result of a subquery

[urshofer]

Describe the bug

First I have to admit: not sure if its a bug or if there is a workaround for the problem.

If I add a colum to a query with a subquery, the column will not be part of the subquery in the where clause which is automatically created if pagination is active (default). Therefore I cannot use this added column in the sort clause, because pagination adds this column also to the subquery in the where clause with min(column)

Stack trace

{
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlState: '42S22',
  sqlMessage: "Unknown column 'sortcontent' in 'order clause'",
  sql: "select `Contribution`.`_name`, `Contribution`.`Id`, `Contribution`.`__sort__`, `d`.`_content`, `f`.`_fieldname`, `t`.`_name`, (select `d0`.`_content` from `_data` as `d0` left join `_templates` as `t1` on `d0`.`_fortemplatefield` = `t1`.`id` where `d0`.`_forcontribution` = `Contribution`.`id` and `t1`.`_fieldname` = 'Lauftext') as `sortcontent` from `_contributions` as `Contribution` left join `_data` as `d` on `Contribution`.`id` = `d`.`_forcontribution` left join `_templates` as `f` on `d`.`_fortemplatefield` = `f`.`id` left join `_templatenames` as `t` on `f`.`_fortemplate` = `t`.`id` where `Contribution`.`id` in (select `Contribution`.`id` from (select `Contribution`.`id` from `_contributions` as `Contribution` left join `_data` as `d` on `Contribution`.`id` = `d`.`_forcontribution` left join `_templates` as `f` on `d`.`_fortemplatefield` = `f`.`id` left join `_templatenames` as `t` on `f`.`_fortemplate` = `t`.`id` where `d`.`_content` like '%Skulptur%' and `f`.`_fieldname` in ('Titel', 'Werktitel', 'Lead') and (`t`.`_name` like '%Nachrichten%' or `t`.`_name` like '%Publikation%') group by `Contribution`.`id` order by min(`sortcontent`) asc limit 3) as `Contribution`) order by `sortcontent` asc"
}

To Reproduce
Steps to reproduce the behavior:

1. Create a Subquery

  const qb1 = em.createQueryBuilder(Data)
    .select('Content')
    .where({ 
      Forcontribution: em.getKnex().ref('Contribution.id'),
      Fortemplatefield: {
        Fieldname: 'Lauftext'
      }
    }).as('sortcontent');

2. Use this subquery in the main query

  const qb = em.createQueryBuilder(Contributions, 'Contribution')
  qb.select(['*', qb1])
    .where({
      Datas: {
        Content: {$like: '%Skulptur%'}, 
      }
    })
    .limit(3)
    .orderBy({'sortcontent': 'ASC'})    
    await qb.execute()

Expected behavior
I'd like to sort by the columnt sortcontent as well.

Additional context
Add any other context about the problem here.

Versions

Dependency	Version
node	16.15.1
typescript	4.9.5
mikro-orm	5.6.13
@mikro-orm/mysql	5.6.13

[B4nan]

I don't think you can order by an alias, it would have to be an expression - so the subquery itself.

[urshofer]

… what is beyond my knowledge since it seems to be more complicated than orderBy(qb1)...

[B4nan]

Hmm maybe not, we have an example in the docs that is using subquery for ordering:

const knex = em.getKnex();
const qb1 = em.createQueryBuilder(Book2, 'b').count('b.uuid', true).where({ author: knex.ref('a.id') }).as('Author2.booksTotal');
const qb2 = em.createQueryBuilder(Author2, 'a');
qb2.select(['*', qb1]).orderBy({ booksTotal: 'desc' });

console.log(qb2.getQuery());
// select `a`.*, (select count(distinct `b`.`uuid_pk`) as `count` from `book2` as `b` where `b`.`author_id` = `a`.`id`) as `books_total` from `author2` as `a` order by `books_total` desc

https://mikro-orm.io/docs/query-builder#using-sub-queries

The code is also part of the test suite. I just confirmed I can also execute the query (the tests are only asserting how it looks).

Maybe try to define the virtual property for it and use the same approach, aliasing with the entity name (note the .as('Author2.booksTotal')).

edit: the docs even explicitly mention The dynamic property (booksTotal) needs to be defined at the entity level (as persist: false).

[urshofer]

True, this works (and I derived my code from this example). But does it also work with
qb2.select(['*', qb1]).limit(1).orderBy({ booksTotal: 'desc' })? If I leave out the where() and limit() part, also my code above works.

[B4nan]

Right, now I see what's happening. I thought the problem is in the outer order by, but it's the inner one. Yeah that sounds like something fixable, I guess it should be omitted from the inner order by, as it has no effect on it?

[urshofer]

Not sure if the inner orderBy can be omitted? It does affect the pagination, not?

[B4nan]

True, it needs to be where the limit is applied.

[urshofer]

Since I'm evaluating mikro orm for a project for just a day, It's hard for me to contribute with a pull request. Maybe you can point me to the right direction so I could start thinking of a fix?

[B4nan]

This is where the pagination happens:

https://github.com/mikro-orm/mikro-orm/blob/master/packages/knex/src/query/QueryBuilder.ts#L1055