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)
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,columnhave wrong value inside. For example, if we have expressionjoinedTable.someColumn is not null, variablecolumnwill havesomeColumn is not nullvalue. 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:query.addSelect('case when (joinedTable.someColumn = :someValue) then 1 else 0 end', 'customOrderField')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