[deepkit/orm] query.count() throws an error if used with query pagination

[pxDot]

Constructing a query like this

let dbQuery = this.itemsPerPage(itemsPerPage).page(page);
const [items, total] = await Promise.all([dbQuery.find(), dbQuery.count()]);

throws

[ERROR] Controller error TypeError: Cannot read properties of undefined (reading 'count')

for page > 1. Its fine for the first page but as soon as the page is incremented it is throwing.

My current workaround looks like this now:

const [items, total] = await Promise.all([dbQuery.itemsPerPage(itemsPerPage).page(page).find(), dbQuery.count()]);

[marcj]

do you have the full stack trace so we see where exactly .count is read?

[pxDot]

Sure, here we go:

[ERROR] Controller error TypeError: Cannot read properties of undefined (reading 'count')
    at PostgresSQLQueryResolver.count (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_c3ef3d8ec3a102af1c7fc46c29499a99/node_modules/@deepkit/sql/src/sql-adapter.ts:252:31)
    at processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async _a.count (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]_@de_85999d0550f0eef3e8102214150fa24c/node_modules/@deepkit/orm/src/query.ts:839:20)
    at async Promise.all (index 1)
    at async _a.findPaginated (/home/[redacted]/[redacted]/packages/models/src/pagination.model.ts:42:28)
    at async HttpListener.onController (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/http.ts:701:26)
    at async self (eval at buildAsync (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]/node_modules/@deepkit/core/src/compiler.ts:129:20), <anonymous>:207:29)
    at async HttpKernel.handleRequest (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/kernel.ts:121:17)

[pxDot]

Found another issue with count and orderBy queries:

// this === instanceof Query
const dbQuery = this.orderBy(field, direction);
const [items, total] = await Promise.all([dbQuery.itemsPerPage(itemsPerPage).page(page).find(), dbQuery.count()]);

fails with

[ERROR] Controller error DatabaseError: column "user.email" must appear in the GROUP BY clause or be used in an aggregate function
    at ensureDatabaseError (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]_@de_85999d0550f0eef3e8102214150fa24c/node_modules/@deepkit/orm/src/type.ts:32:12)
    at PostgresStatement.get (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]__6ef5ff80bccbe5ecd7bdef869daab43a/node_modules/@deepkit/postgres/src/postgres-adapter.ts:104:40)
    at processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PostgresConnection.execAndReturnSingle (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_c3ef3d8ec3a102af1c7fc46c29499a99/node_modules/@deepkit/sql/src/sql-adapter.ts:131:20)
    ... 4 lines matching cause stack trace ...
    at async HttpListener.onController (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/http.ts:701:26)
    at async self (eval at buildAsync (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]/node_modules/@deepkit/core/src/compiler.ts:129:20), <anonymous>:207:29)
    at async HttpKernel.handleRequest (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/kernel.ts:121:17) {
  [cause]: error: column "user.email" must appear in the GROUP BY clause or be used in an aggregate function
      at /home/[redacted]/[redacted]/node_modules/.pnpm/[email protected]/node_modules/pg/lib/client.js:545:17
      at processTicksAndRejections (node:internal/process/task_queues:105:5)
      at async asyncOperation (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]/node_modules/@deepkit/core/src/core.ts:533:16)
      at async PostgresStatement.get (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]__6ef5ff80bccbe5ecd7bdef869daab43a/node_modules/@deepkit/postgres/src/postgres-adapter.ts:99:25)
      at async PostgresConnection.execAndReturnSingle (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_c3ef3d8ec3a102af1c7fc46c29499a99/node_modules/@deepkit/sql/src/sql-adapter.ts:131:20)
      at async PostgresSQLQueryResolver.count (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_c3ef3d8ec3a102af1c7fc46c29499a99/node_modules/@deepkit/sql/src/sql-adapter.ts:249:25)
      at async _a.count (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]_@de_85999d0550f0eef3e8102214150fa24c/node_modules/@deepkit/orm/src/query.ts:839:20)
      at async Promise.all (index 1)
      at async _a.findPaginated (/home/[redacted]/[redacted]/packages/models/src/pagination.model.ts:51:28)
      at async HttpListener.onController (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/http.ts:701:26)
      at processTicksAndRejections (node:internal/process/task_queues:105:5)
      at async PostgresStatement.get (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]__6ef5ff80bccbe5ecd7bdef869daab43a/node_modules/@deepkit/postgres/src/postgres-adapter.ts:99:25)
      at async PostgresConnection.execAndReturnSingle (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_c3ef3d8ec3a102af1c7fc46c29499a99/node_modules/@deepkit/sql/src/sql-adapter.ts:131:20)
      at async PostgresSQLQueryResolver.count (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_c3ef3d8ec3a102af1c7fc46c29499a99/node_modules/@deepkit/sql/src/sql-adapter.ts:249:25)
      at async _a.count (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected]_@[email protected]_@de_85999d0550f0eef3e8102214150fa24c/node_modules/@deepkit/orm/src/query.ts:839:20)
      at async Promise.all (index 1)
      at async _a.findPaginated (/home/[redacted]/[redacted]/packages/models/src/pagination.model.ts:51:28)
      at async HttpListener.onController (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/http.ts:701:26)
      at async self (eval at buildAsync (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]/node_modules/@deepkit/core/src/compiler.ts:129:20), <anonymous>:207:29)
      at async HttpKernel.handleRequest (/home/[redacted]/[redacted]/node_modules/.pnpm/@[email protected]_46a72f28d0a5204ddf062ecde0b941e8/node_modules/@deepkit/http/src/kernel.ts:121:17) {
    length: 173,
    severity: 'ERROR',
    code: '42803',
    detail: undefined,
    hint: undefined,
    position: '48',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_agg.c',
    line: '1439',
    routine: 'check_ungrouped_columns_walker'
  }
}

I am workarounding this by cloning the query into another variable without the orderBy and pagination queries and executing the count for this, as I am modifying the query before with search queries. In the example I've given this would be sufficient to make it work:

// this === instanceof Query
const dbQuery = this.orderBy(field, direction);
const [items, total] = await Promise.all([dbQuery.itemsPerPage(itemsPerPage).page(page).find(), this.count()]);