crud
crud copied to clipboard
Published
20 hours ago •
nestjsx
nestjsx
Duplicate column in getMany with join + pagination
Bug Report
getMany throws SQL error when join, limit is used together
it's only throwing an error when trying to get relational data with pagination
Current behavior
throwing SQL error: Duplicate column name:-
"SELECT DISTINCT 'distinctAlias'.'Roles_id' AS 'ids_Roles_id' FROM (SELECT 'Roles'.'id' AS 'Roles_id', 'Roles'.'id' AS 'Roles_id', 'Roles'.'createdAt' AS 'Roles_createdAt', 'Roles'.'updatedAt' AS 'Roles_updatedAt', 'Roles'.'name' AS 'Roles_name', 'users'.'id' AS 'users_id', 'users'.'id' AS 'users_id', 'users'.'name' AS 'users_name', 'users'.'email' AS 'users_email' FROM 'Roles' 'Roles' LEFT JOIN 'user-roles' 'users_Roles' ON 'users_Roles'.'Role'='Roles'.'id' LEFT JOIN 'User' 'users' ON 'users'.'id'='users_Roles'.'User') 'distinctAlias' ORDER BY 'Roles_id' ASC LIMIT 2",
Input Code
/roles?join=users&limit=2&page=1
Expected behavior
should return
{
"data": [
{
"id": 1,
"createdAt": "2022-05-27T07:45:29.435Z",
"updatedAt": "2022-05-27T07:45:29.435Z",
"name": "admin",
"users": [
{
"id": 1,
"createdAt": "2022-05-27T07:45:59.381Z",
"updatedAt": "2022-05-27T07:45:59.381Z",
"name": "admin",
"phone": "9876543210",
"email": "[email protected]",
"isActive": false
}
]
},
],
"count": 1,
"total": 6,
"page": 1,
"pageCount": 6
}
Possible Solution
Tested
It's working with TypeOrm version <= 0.2.45
Reason
maybe because in typeOrm version 0.3.0 Deprecated way of loading entity relations:
Old way of loading entity relations:
userRepository.find({
select: ["id", "firstName", "lastName"]
})
New way of loading entity relations:
userRepository.find({
select: {
id: true,
firstName: true,
lastName: true,
}
})
Environment
Package version: 5.0.0-alpha.3
For Tooling issues:
- Node version: v16.15.0
- Platform: Linux
- Database MySQL
Others:
"typeorm": "^0.3.6"
"@nestjs/typeorm": "^8.0.3",
## Repository with minimal reproduction
temporary solution
temporary solution
Didn't solve for me, using "@nestjs/typeorm": "^8.1.4",
Add command: Query
Sending query command: SELECT DISTINCT `distinctAlias`.`Product_id` AS `ids_Product_id` FROM (SELECT `Product`.`id` AS `Product_id`, `Product`.`id` AS `Product_id`, `Product`.`user_id` AS `Product_user_id`, `Product`.`hotel_id` AS `Product_hotel_id`, `Product`.`name` AS `Product_name`, `Product`.`updated_at` AS `Product_updated_at`, `Product`.`created_at` AS `Product_created_at`, `hotel`.`id` AS `hotel_id`, `hotel`.`id` AS `hotel_id`, `hotel`.`name` AS `hotel_name`, `hotel`.`updated_at` AS `hotel_updated_at`, `hotel`.`created_at` AS `hotel_created_at` FROM `product` `Product` LEFT JOIN `hotel` `hotel` ON `hotel`.`id`=`Product`.`hotel_id`) `distinctAlias` ORDER BY `Product_id` ASC LIMIT 100
0 159 <== Query#unknown name(0,,680)
0 159 <== a40200000353454c4543542044495354494e4354206064697374696e6374416c696173602e6050726f647563745f69646020415320606964735f50726f647563745f6964602046524f4d202853454c454354206050726f64756374602e60696460204153206050726f647563745f6964602c206050726f64756374602e60696460204153206050726f647563745f6964602c206050726f64756374602e60757365725f696460204153206050726f647563745f757365725f6964602c206050726f64756374602e60686f74656c5f696460204153206050726f647563745f686f74656c5f6964602c206050726f64756374602e606e616d6560204153206050726f647563745f6e616d65602c206050726f64756374602e60757064617465645f617460204153206050726f647563745f757064617465645f6174602c206050726f64756374602e60637265617465645f617460204153206050726f647563745f637265617465645f6174602c2060686f74656c602e606964602041532060686f74656c5f6964602c2060686f74656c602e606964602041532060686f74656c5f6964602c2060686f74656c602e606e616d65602041532060686f74656c5f6e616d65602c2060686f74656c602e60757064617465645f6174602041532060686f74656c5f757064617465645f6174602c2060686f74656c602e60637265617465645f6174602041532060686f74656c5f637265617465645f6174602046524f4d206070726f6475637460206050726f6475637460204c454654204a4f494e2060686f74656c602060686f74656c60204f4e2060686f74656c602e606964603d6050726f64756374602e60686f74656c5f69646029206064697374696e6374416c69617360204f52444552204259206050726f647563745f69646020415343204c494d495420313030
raw: ff24042334325332314475706c696361746520636f6c756d6e206e616d65202750726f647563745f696427
Trace:
at PoolConnection.handlePacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:427:17)
at PacketParser.onPacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:85:12)
at PacketParser.executeStart (C:\dev\hotelsathome-api\node_modules\mysql2\lib\packet_parser.js:75:16)
at Socket.<anonymous> (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:92:25)
at Socket.emit (node:events:527:28)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)
at Socket.Readable.push (node:internal/streams/readable:228:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
0 159 ==> Query#unknown name(1,Error,47)
[Nest] 6420 - 06/20/2022, 8:22:41 PM ERROR [ExceptionsHandler] Duplicate column name 'Product_id'
QueryFailedError: Duplicate column name 'Product_id'
at Query.onResult (C:\dev\hotelsathome-api\src\driver\mysql\MysqlQueryRunner.ts:222:33)
at Query.execute (C:\dev\hotelsathome-api\node_modules\mysql2\lib\commands\command.js:36:14)
at PoolConnection.handlePacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:456:32)
at PacketParser.onPacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:85:12)
at PacketParser.executeStart (C:\dev\hotelsathome-api\node_modules\mysql2\lib\packet_parser.js:75:16)
at Socket.<anonymous> (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:92:25)
at Socket.emit (node:events:527:28)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)
at Socket.Readable.push (node:internal/streams/readable:228:10)
But it is working on typeorm ^0.2.45
Didn't solve for me, using
"@nestjs/typeorm": "^8.1.4",Add command: Query Sending query command: SELECT DISTINCT `distinctAlias`.`Product_id` AS `ids_Product_id` FROM (SELECT `Product`.`id` AS `Product_id`, `Product`.`id` AS `Product_id`, `Product`.`user_id` AS `Product_user_id`, `Product`.`hotel_id` AS `Product_hotel_id`, `Product`.`name` AS `Product_name`, `Product`.`updated_at` AS `Product_updated_at`, `Product`.`created_at` AS `Product_created_at`, `hotel`.`id` AS `hotel_id`, `hotel`.`id` AS `hotel_id`, `hotel`.`name` AS `hotel_name`, `hotel`.`updated_at` AS `hotel_updated_at`, `hotel`.`created_at` AS `hotel_created_at` FROM `product` `Product` LEFT JOIN `hotel` `hotel` ON `hotel`.`id`=`Product`.`hotel_id`) `distinctAlias` ORDER BY `Product_id` ASC LIMIT 100 0 159 <== Query#unknown name(0,,680) 0 159 <== a40200000353454c4543542044495354494e4354206064697374696e6374416c696173602e6050726f647563745f69646020415320606964735f50726f647563745f6964602046524f4d202853454c454354206050726f64756374602e60696460204153206050726f647563745f6964602c206050726f64756374602e60696460204153206050726f647563745f6964602c206050726f64756374602e60757365725f696460204153206050726f647563745f757365725f6964602c206050726f64756374602e60686f74656c5f696460204153206050726f647563745f686f74656c5f6964602c206050726f64756374602e606e616d6560204153206050726f647563745f6e616d65602c206050726f64756374602e60757064617465645f617460204153206050726f647563745f757064617465645f6174602c206050726f64756374602e60637265617465645f617460204153206050726f647563745f637265617465645f6174602c2060686f74656c602e606964602041532060686f74656c5f6964602c2060686f74656c602e606964602041532060686f74656c5f6964602c2060686f74656c602e606e616d65602041532060686f74656c5f6e616d65602c2060686f74656c602e60757064617465645f6174602041532060686f74656c5f757064617465645f6174602c2060686f74656c602e60637265617465645f6174602041532060686f74656c5f637265617465645f6174602046524f4d206070726f6475637460206050726f6475637460204c454654204a4f494e2060686f74656c602060686f74656c60204f4e2060686f74656c602e606964603d6050726f64756374602e60686f74656c5f69646029206064697374696e6374416c69617360204f52444552204259206050726f647563745f69646020415343204c494d495420313030 raw: ff24042334325332314475706c696361746520636f6c756d6e206e616d65202750726f647563745f696427 Trace: at PoolConnection.handlePacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:427:17) at PacketParser.onPacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:85:12) at PacketParser.executeStart (C:\dev\hotelsathome-api\node_modules\mysql2\lib\packet_parser.js:75:16) at Socket.<anonymous> (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:92:25) at Socket.emit (node:events:527:28) at addChunk (node:internal/streams/readable:315:12) at readableAddChunk (node:internal/streams/readable:289:9) at Socket.Readable.push (node:internal/streams/readable:228:10) at TCP.onStreamRead (node:internal/stream_base_commons:190:23) 0 159 ==> Query#unknown name(1,Error,47)[Nest] 6420 - 06/20/2022, 8:22:41 PM ERROR [ExceptionsHandler] Duplicate column name 'Product_id' QueryFailedError: Duplicate column name 'Product_id' at Query.onResult (C:\dev\hotelsathome-api\src\driver\mysql\MysqlQueryRunner.ts:222:33) at Query.execute (C:\dev\hotelsathome-api\node_modules\mysql2\lib\commands\command.js:36:14) at PoolConnection.handlePacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:456:32) at PacketParser.onPacket (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:85:12) at PacketParser.executeStart (C:\dev\hotelsathome-api\node_modules\mysql2\lib\packet_parser.js:75:16) at Socket.<anonymous> (C:\dev\hotelsathome-api\node_modules\mysql2\lib\connection.js:92:25) at Socket.emit (node:events:527:28) at addChunk (node:internal/streams/readable:315:12) at readableAddChunk (node:internal/streams/readable:289:9) at Socket.Readable.push (node:internal/streams/readable:228:10)But it is working on
typeorm ^0.2.45
Query: { exclude: ['id'] , join: {} }
Working thx @jongomes !
typeorm : 0.3.7
@nestjsx/crud : 5.0.0-alpha.3
@jongomes -> https://www.npmjs.com/package/@rewiko/crud this repo solves everything...
This is because in typeorm-service.ts: the select params return two id:
getSelect(query, options) {
const allowed = this.getAllowedColumns(this.entityColumns, options);
const columns = query.fields && query.fields.length
? query.fields.filter((field) => allowed.some((col) => field === col))
: allowed;
const select = [
...(options.persist && options.persist.length ? options.persist : []),
...columns,
...this.entityPrimaryColumns,
].map((col) => `${this.alias}.${col}`);
console.log('-------->select',select)
console.log('-------->columns',columns)
console.log('-------->entityPrimaryColumns',this.entityPrimaryColumns)
return select;
}
one from allowed, one from this.entityPrimaryColumns.
Working thx @jongomes !
typeorm : 0.3.7 @nestjsx/crud : 5.0.0-alpha.3
This is work for me. Thx :)
