Duplicate column in getMany with join + pagination #788

Aditya1942 · 2022-05-27T14:12:05Z

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": "admin@gmail.com",
        "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

The text was updated successfully, but these errors were encountered:

Coystark · 2022-05-30T22:43:04Z

Same #777

glothos · 2022-06-01T22:21:04Z

+1 same here.

jongomes · 2022-06-02T14:29:58Z

temporary solution

Aditya1942 · 2022-06-06T20:11:22Z

temporary solution

Thanks Bro

jongomes · 2022-06-06T20:15:03Z

https://github.com/rewiko/crud let go repo

Gustavo-Contreiras-Hotels-at-Home · 2022-06-21T00:23:21Z

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

jongomes · 2022-06-21T00:45:44Z

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

Query: { exclude: ['id'] , join: {} }

jongomes · 2022-06-21T00:50:21Z

https://www.npmjs.com/package/@rewiko/crud

MikhaelGerbet · 2022-07-11T21:43:58Z

Working thx @jongomes !

typeorm : 0.3.7
@nestjsx/crud : 5.0.0-alpha.3

rodrigo1991 · 2022-07-15T01:56:54Z

@jongomes -> https://www.npmjs.com/package/@rewiko/crud
this repo solves everything...

jongomes · 2022-07-15T02:08:22Z

@rodrigo1991 yes, I use him

superiums · 2022-09-12T01:45:47Z

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.

Jay-flow · 2023-04-20T09:05:13Z

Working thx @jongomes !
typeorm : 0.3.7
@nestjsx/crud : 5.0.0-alpha.3

This is work for me. Thx :)

Aditya1942 changed the title ~~Duplicate column in getMany join + limit + page~~ Duplicate column in getMany with join + pagination May 28, 2022

qpfmtlcp mentioned this issue Aug 5, 2022

fix: duplicate column selection rewiko/crud#20

Merged

This was referenced Mar 20, 2024

Fix: duplicate column selection issue in getMany with joins and pagination gid-oss/dataui-nestjs-crud#33

Closed

Fix: duplicate column selection issue in getMany with joins and pagination gid-oss/dataui-nestjs-crud#34

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Duplicate column in getMany with join + pagination #788

Duplicate column in getMany with join + pagination #788

Aditya1942 commented May 27, 2022

Coystark commented May 30, 2022

glothos commented Jun 1, 2022

jongomes commented Jun 2, 2022

Aditya1942 commented Jun 6, 2022

jongomes commented Jun 6, 2022

Gustavo-Contreiras-Hotels-at-Home commented Jun 21, 2022 •

edited

jongomes commented Jun 21, 2022

jongomes commented Jun 21, 2022

MikhaelGerbet commented Jul 11, 2022

rodrigo1991 commented Jul 15, 2022

jongomes commented Jul 15, 2022

superiums commented Sep 12, 2022

Jay-flow commented Apr 20, 2023

Duplicate column in getMany with join + pagination #788

Duplicate column in getMany with join + pagination #788

Comments

Aditya1942 commented May 27, 2022

Bug Report

Current behavior

Input Code

Expected behavior

Possible Solution

Tested

Reason

Old way of loading entity relations:

New way of loading entity relations:

Environment

Coystark commented May 30, 2022

glothos commented Jun 1, 2022

jongomes commented Jun 2, 2022

Aditya1942 commented Jun 6, 2022

jongomes commented Jun 6, 2022

Gustavo-Contreiras-Hotels-at-Home commented Jun 21, 2022 • edited

jongomes commented Jun 21, 2022

jongomes commented Jun 21, 2022

MikhaelGerbet commented Jul 11, 2022

rodrigo1991 commented Jul 15, 2022

jongomes commented Jul 15, 2022

superiums commented Sep 12, 2022

Jay-flow commented Apr 20, 2023

Gustavo-Contreiras-Hotels-at-Home commented Jun 21, 2022 •

edited