typeorm: ORDER BY IS NULL breaks typeorm when .take/.skip are used with JOIN

Issue type:

[x] question [ ] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [x] mysql / mariadb [ ] oracle [ ] postgres [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Following advice in https://github.com/typeorm/typeorm/issues/1412

I was trying to add this to my querry, but error (given at end) occured.

Steps to reproduce.

Take simple query:

await typeorm.getManager().getRepository(Player).createQueryBuilder("player")
    .take(10)
    .skip(10)
    .addOrderBy("player.id IS NOT NULL","DESC")
    .getMany();

This works.

Add join to it (1to1):

    await typeorm.getManager().getRepository(Player).createQueryBuilder("player")
    .leftJoinAndSelect("player.ranking","player_ranking")
    .take(10)
    .skip(10)
    .addOrderBy("player.id IS NOT NULL","DESC")
    .getMany();

And error will appear. What’s more, removing take/skip like this:

await typeorm.getManager().getRepository(Player).createQueryBuilder("player")
    .leftJoinAndSelect("player.ranking","player_ranking")
    .addOrderBy("player.id IS NOT NULL","DESC")
    .getMany();

Will work correctly. This applies to getMany, getOne, getManyAndCount (checked by my).

Error produced is:

UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'databaseName' of undefined
    at {...}/query-builder/SelectQueryBuilder.ts:1866:114
    at Array.map (<anonymous>)
    at SelectQueryBuilder.createOrderByCombinedWithSelectExpression ({...}/query-builder/SelectQueryBuilder.ts:1861:14)
    at SelectQueryBuilder.<anonymous> (/{...}/query-builder/SelectQueryBuilder.ts:1779:46)
    at step ({...}/node_modules/typeorm/query-builder/SelectQueryBuilder.js:42:23)
    at Object.next ({...}/node_modules/typeorm/query-builder/SelectQueryBuilder.js:23:53)
    at {...}/node_modules/typeorm/query-builder/SelectQueryBuilder.js:17:71
    at new Promise (<anonymous>)
    at __awaiter (/Users/atom/Projekty/atom/107/dcs-api/node_modules/typeorm/query-builder/SelectQueryBuilder.js:13:12)
    at SelectQueryBuilder.executeEntitiesAndRawResults ({...}/node_modules/typeorm/query-builder/SelectQueryBuilder.js:1377:16)
(node:26326) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 3)
(node:26326) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

About this issue

  • Original URL
  • State: open
  • Created 5 years ago
  • Reactions: 16
  • Comments: 19 (3 by maintainers)

Most upvoted comments

Any progress on this issue ?

The problem is here. https://github.com/typeorm/typeorm/blob/a858de1f195d41aaffdeb8c10faa538dc1a15f5c/src/query-builder/SelectQueryBuilder.ts#L1875 Typeorm does not support complex ORDER BY expressions for innerJoined table values. It just parses your expression using .split("."). So, of course, column have wrong value inside. For example, if we have expression joinedTable.someColumn is not null, variable column will have someColumn is not null value. This is where this bug takes a start. The workaround is to use custom case inside of query. In example case i could make something like this:

  1. add selection like this query.addSelect('case when (joinedTable.someColumn = :someValue) then 1 else 0 end', 'customOrderField')
  2. add sort by customOrderField like this query.addOrderBy('customOrderField','DESC','NULLS LAST')

Hi, any newness here? I have the same problem using nestjs.

@jagabs my gut is your .addOrderBy needs to refer the name of the relation on the entity not the column name. Try that