Select too much verbose (specially with relations)

[luixal]

Hi,

This issue is clearly related to #869 but the solution provided in that issue is not working for me.

If I use it like this, relations are loaded properly:

return paginate(
      query,
      this.placeRepository,
      {
        sortableColumns: ['code', 'name', 'peopleCounter', 'createdAt', 'updatedAt'],
        defaultSortBy: [['updatedAt', 'DESC'], ['createdAt', 'DESC']],
        searchableColumns: ['code', 'name'],
        filterableColumns: {
          code: true,
          name: true,
          description: true,
          type: true,
          peopleCounter: true,
          createdAt: true,
          updatedAt: true
        },
        relations: ['parent', 'children']
      }
    )

like in this result:

// query:
// /places?select=id,code,name,children
// this is also weird, select should work although no select config field provided, don't you think?

{
    "data": [
        {
            "id": 28,
            "code": "P01",
            "name": "Place P01",
            "description": "Description for root place P01",
            "type": "Place",
            "peopleCounter": 0,
            "createdAt": "2024-07-12T20:26:25.323Z",
            "updatedAt": "2024-07-12T20:26:25.323Z",
            "parent": null,
            "children": [
                {
                    "id": 29,
                    "code": "SP028.0",
                    "name": "SubPlace SP028.0",
                    "description": "Description for sub-place SP028.0",
                    "type": "Place",
                    "peopleCounter": 0,
                    "createdAt": "2024-07-12T20:26:25.470Z",
                    "updatedAt": "2024-07-12T20:26:25.470Z"
                },
                {
                    "id": 30,
                    "code": "SP028.1",
                    "name": "SubPlace SP028.1",
                    "description": "Description for sub-place SP028.1",
                    "type": "Place",
                    "peopleCounter": 0,
                    "createdAt": "2024-07-12T20:26:25.546Z",
                    "updatedAt": "2024-07-12T20:26:25.546Z"
                }
            ]
        }
    ]
}

but if I include 'children' the select field:

return paginate(
      query,
      this.placeRepository,
      {
        sortableColumns: ['code', 'name', 'peopleCounter', 'createdAt', 'updatedAt'],
        defaultSortBy: [['updatedAt', 'DESC'], ['createdAt', 'DESC']],
        searchableColumns: ['code', 'name'],
        select: ['id', 'code', 'name', 'description', 'type', 'peopleCounter', 'createdAt', 'updatedAt', 'children'],
        filterableColumns: {
          code: true,
          name: true,
          description: true,
          type: true,
          peopleCounter: true,
          createdAt: true,
          updatedAt: true
        },
        relations: ['parent', 'children']
      }
    )

I get this error:

ERROR [ExceptionsHandler] Error: Invalid column name 'children'.
QueryFailedError: Error: Invalid column name 'children'.
    at <anonymous> (/home/nemesis/dev/clece/cle-inn/coriot/node_modules/typeorm/driver/src/driver/sqlserver/SqlServerQueryRunner.ts:282:30)
    at /home/nemesis/dev/clece/cle-inn/coriot/node_modules/mssql/lib/base/request.js:440:25
    at Request.userCallback (/home/nemesis/dev/clece/cle-inn/coriot/node_modules/mssql/lib/tedious/request.js:492:15)
    at Request.Request.callback (/home/nemesis/dev/clece/cle-inn/coriot/node_modules/tedious/src/request.ts:379:14)
    at onEndOfMessage (/home/nemesis/dev/clece/cle-inn/coriot/node_modules/tedious/src/connection.ts:3713:22)
    at Object.onceWrapper (node:events:632:28)
    at Parser.emit (node:events:518:28)
    at Readable.<anonymous> (/home/nemesis/dev/clece/cle-inn/coriot/node_modules/tedious/src/token/token-stream-parser.ts:30:12)
    at Readable.emit (node:events:518:28)
    at endReadableNT (node:internal/streams/readable:1696:12)

Adding the 'children.id' as proposed in the related issue makes the relation work, but it only includes the 'id' field. If I have all fields to be included, I have to write all of them, like this:

select: [
  'id', 'code', 'name', 'description', 'type', 'peopleCounter', 'createdAt', 'updatedAt',
  'children.id', 'children.code', 'children.name', 'children.description', 'children.type', 'children.peopleCounter', 'children.createdAt', 'children.updatedAt'],

I have FOUR relations just in this entity. That's insane.

What about making select work in queries by default? I mean, making this /places?select=id,code,name,children filter the fields returned without setting up a select field in config.

Or What about using wildcards? Like this:

// make this:
select: [
  'id', 'code', 'name', 'description', 'type', 'peopleCounter', 'createdAt', 'updatedAt',
  'children.id', 'children.code', 'children.name', 'children.description', 'children.type', 'children.peopleCounter', 'children.createdAt', 'children.updatedAt']
// writeable like this:
select: ['*', 'children.*']

Any of those would be really helpful :)

[samislam]

Did you find a better solution?

[ppetzold]

sounds like a useful enhancement. happy to accept PR