Xata•7mo ago

Filter with link table json field

I've table call students and it has a link field called class to table called classes. Normally I can filter without any problems using like this, xata.db.classes.filter({ "grade->v1" : 13 " }).getAll() How can I filter using student table like that. I tried some of these. But none of them worked. xata.db.students.filter({ "class.grade->v1" : 13 " }).getAll() xata.db.students.filter({ "class->grade->v1" : 13 " }).getAll() Is it possible to filter from the linked table to json fileds like that?

7 Replies

kostas•7mo ago

It seems the REST API does not support referring to json fields through link columns. You can achieve this via SQL though with a xata.sql call (https://xata.io/docs/sdk/sql/overview)

await xata.sql`SELECT * FROM "students" LEFT JOIN classes ON students.class = classes.id WHERE (grade->'v1')::numeric = 13;`

await xata.sql`SELECT * FROM "students" LEFT JOIN classes ON students.class = classes.id WHERE (grade->'v1')::numeric = 13;`

dwalker93OP•7mo ago

@kostas thank you sir.

dwalker93OP•7mo ago

And found a bug which is we can't sort reverse link results with columns like ID, createdAt, updatedAt or our custom datetime field as mentioned in the example in the docs.(https://xata.io/docs/concepts/data-model#links-and-relationships)

Data model

A relational data model with schemaful flexibility and JSON support

dwalker93OP•7mo ago

Issue: https://github.com/xataio/client-ts/issues/1474

GitHub

xata revese link sorting with createdAt field throw unknown error ·...

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#l...

kostas•7mo ago

Hi, sorting on xata. internal fields is not supported with reverse links. We will update docs as the direct docs example can be misleading and frustrating. Apologies for the inconvenience caused by it. For now, you will need to create and populate another createdAt datetime column and also explicitly select it in the query, in order to sort on it under a reverse link. 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();

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.

kostas•7mo ago

As an alternative, you can use a Postgres-enabled (Beta) database: https://xata.io/docs/postgres The internal columns in these databases are renamed to xata_createdat/xata_updatedat. These can be used for reverse link sorting. To use the Postgres enabled databases, install the Xata client @next version, as mentioned at the top of the linked docs page.

Connect to Postgres

Learn how to connect to Postgres directly via the wire protocol

dwalker93OP•7mo ago

thank you @kostas ❤️

Gaming

Programming

Filter with link table json field