xata revese link sorting with createdAt field throw unknown error

[dwalker93]

Discussed in #1471

^{Originally posted by dwalker93 May 7, 2024}
In xata reverse link can't sort results with createdAt date. Followed as official guide (https://xata.io/docs/concepts/data-model#links-and-relationships) but no luck. Giving error,

    FetcherError: Internal Error (Request ID: f3b4a5dc-99e9-94a4-87a8-cfa06828ce11)
        at eval (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:2778:15)
        at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
        at async defaultTrace (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:196:10)
        at async fetch$1 (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:2721:10)
        at async eval (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:5833:42)
        at async defaultTrace (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:196:10)
        at async _Query.getIterator (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:5244:16)
        at async _Query.getAll (webpack-internal:///(action-browser)/./node_modules/.pnpm/@[email protected][email protected]/node_modules/@xata.io/client/dist/index.mjs:5272:22)
        at async $$ACTION_0 (webpack-internal:///(action-browser)/./actions/student.ts:23:25)
        at async D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\compiled\next-server\app-page.runtime.dev.js:39:418
        at async rS (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\compiled\next-server\app-page.runtime.dev.js:38:7978)
        at async r2 (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\compiled\next-server\app-page.runtime.dev.js:41:1251)
        at async doRender (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:1438:30)
        at async cacheEntry.responseCache.get.routeKind (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:1599:28)
        at async DevServer.renderToResponseWithComponentsImpl (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:1507:28)
        at async DevServer.renderPageComponent (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:1924:24)        
        at async DevServer.renderToResponseImpl (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:1962:32)       
        at async DevServer.pipeImpl (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:920:25)
        at async NextNodeServer.handleCatchallRenderRequest (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\next-server.js:272:17)
        at async DevServer.handleRequestImpl (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\base-server.js:816:17)
        at async D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\dev\next-dev-server.js:339:20
        at async Span.traceAsyncFn (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\trace\trace.js:154:20)
        at async DevServer.handleRequest (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\dev\next-dev-server.js:336:24)       
        at async invokeRender (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\lib\router-server.js:174:21)
        at async handleRequest (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\lib\router-server.js:353:24)
        at async requestHandlerImpl (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\lib\router-server.js:377:13)
        at async Server.requestListener (D:\projects\eclz\node_modules\.pnpm\[email protected][email protected][email protected][email protected]\node_modules\next\dist\server\lib\start-server.js:141:13) {
      status: 500,
      errors: [
        {
          message: 'Internal Error (Request ID: f3b4a5dc-99e9-94a4-87a8-cfa06828ce11)',
          status: 500
        }
      ],
      requestId: 'f3b4a5dc-99e9-94a4-87a8-cfa06828ce11',
      cause: undefined
    }

Tried code snipet,

    await xata.db.students
          .select([
            "firstName",
            "grade",
            {
              name: "<-marks.studentId",
              as: "monthsTop",
              columns: ["month", "score"],
              limit: 1,
              offset: 0,
              sort: [
                {
                  "xata.createdAt": "desc",
                },
              ],
            },
          ])
          .getAll();

tried also simply,

    sort: [
            {
              createdAt: "desc",
            },
           ],

and not working.

But working fine with other column values. such as,

     sort: [
            {
              month: "desc",
            },
           ],

Plaground test

[kostasb]

This is a REST API error which is being tracked with an internal issue.

Sorting in reverse links is not supported with the xata. internal columns. We will update the docs examples to reflect this.

For now, you will need to create another createdAt datetime column and explicitly select it in the query, in order to sort on it.

Here is an example:

const page = await xata.db.users
  .select([
    "name",
    {
      name: "<-posts.author",
      columns: ["title","createdAt"],
      as: "posts",
      limit: 10,
      offset: 0,
      sort: [
        {
          "createdAt": "desc",
        },
      ],
    },
  ])
  .getAll();

where the createdAt column exists in the posts table and is an explicitly created column, not xata.createdAt.

[kostasb]

Closing here as it is not a TypeScript client issue but a REST API issue which is tracked internally.