Eusebio Trigo Posts - Answer Overflow

Eusebio Trigo

Explore posts from servers

•Created by Eusebio Trigo on 4/15/2024 in #help

SQL Query working differently in playground vs SDK

Hi (again), I have the following code:

const subquery = `SELECT DISTINCT (o.id)
                        FROM transactions t,
                             orders o
                        WHERE t.report = '${report_id}'
                          AND t.order = o.id`;

      const total_amounts = await this.dbClient.sql<{
        total_invoiced: number,
        total_commission: number;
      }>`SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
         FROM orders
         WHERE orders.id in (${subquery})`;

const subquery = `SELECT DISTINCT (o.id)
                        FROM transactions t,
                             orders o
                        WHERE t.report = '${report_id}'
                          AND t.order = o.id`;

      const total_amounts = await this.dbClient.sql<{
        total_invoiced: number,
        total_commission: number;
      }>`SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
         FROM orders
         WHERE orders.id in (${subquery})`;

And the result of that query is

{
  records: [ { total_commission: null, total_invoiced: null } ],
  rows: undefined,
  warning: undefined,
  columns: [
    { name: 'total_invoiced', type: 'float8' },
    { name: 'total_commission', type: 'float8' }
  ]
}

{
  records: [ { total_commission: null, total_invoiced: null } ],
  rows: undefined,
  warning: undefined,
  columns: [
    { name: 'total_invoiced', type: 'float8' },
    { name: 'total_commission', type: 'float8' }
  ]
}

But, in the playground, I run the same query:

SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
         FROM orders
         WHERE orders.id in (SELECT DISTINCT (o.id)
                        FROM transactions t,
                             orders o
                        WHERE t.report = 'rec_REDACTED'
                          AND t.order = o.id)

SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
         FROM orders
         WHERE orders.id in (SELECT DISTINCT (o.id)
                        FROM transactions t,
                             orders o
                        WHERE t.report = 'rec_REDACTED'
                          AND t.order = o.id)

And then I get

{
  "records": [
    {
      "total_commission": 2271.84,
      "total_invoiced": 28398
    }
  ],
  "columns": [
    {
      "name": "total_invoiced",
      "type": "float8"
    },
    {
      "name": "total_commission",
      "type": "float8"
    }
  ],
  "total": 0
}

{
  "records": [
    {
      "total_commission": 2271.84,
      "total_invoiced": 28398
    }
  ],
  "columns": [
    {
      "name": "total_invoiced",
      "type": "float8"
    },
    {
      "name": "total_commission",
      "type": "float8"
    }
  ],
  "total": 0
}

Is there anything I may be doing wrong in here? Thanks!!

5 replies

XXata

•Created by Eusebio Trigo on 4/12/2024 in #help

Cannot perform I/O on behalf of a different request in Cloudflare Workers

Ok, so this may be a long one... I have several cloudflare workers that write or request data from Xata. When I do a single request using the TypeScript SDK, it works OK. When I do several requests like in a await Promise.all(array.map(async arrayElem => queryToXata(arrayElem))), then I get an error like:

 
  Unable to retrieve metrics for report rec_REDACTED Error: Cannot perform I/O
  on behalf of a different request. I/O objects (such as streams, request/response bodies, and
  others) created in the context of one request handler cannot be accessed from a different
  request's handler. This is a limitation of Cloudflare Workers which allows us to improve overall
  performance.

 
  Unable to retrieve metrics for report rec_REDACTED Error: Cannot perform I/O
  on behalf of a different request. I/O objects (such as streams, request/response bodies, and
  others) created in the context of one request handler cannot be accessed from a different
  request's handler. This is a limitation of Cloudflare Workers which allows us to improve overall
  performance.

This is coming from the Cloudflare Worker SDK, Wrangler. I have attached the stacktrace. I have tried creating the xata client on each method I call, so it is separated... but to no avail. If you request or need more information, i'll be happy to share code, or even do a screensharing session, etc. I want to discard if this is something from the Xata SDK, or it is something else,...

10 replies

XXata

•Created by Eusebio Trigo on 12/11/2023 in #help

Question about types in the Typescript client

6 replies

XXata

•Created by Eusebio Trigo on 11/13/2023 in #help

Running queries with DISTINCT

Hi team! Is there a way to run queries with something like .distinct(["column_name"])? We know about summarize (https://xata.io/docs/sdk/summarize#what-can-summarize-do), but it has a limit on 1000 results (and defaults to 20). We are aware of the alternative using code and filtering out repeated results... Or running direct SQL queries to the API. What would be the recommended way? Thanks!

5 replies

XXata

•Created by Eusebio Trigo on 11/11/2023 in #help

Different results using summarize vs filtering

Hi team (it's me, again), I'm seeing that, at least in the playground, there is a difference of results when using summaries vs using filtering query (I'll add examples). To make it short, summaries are only returning 20 results, whereas it should be returning more (22, in our current data set). Is there a limit of the number of results provided by a summarize call? It is returning only 20 results. I would like you guys to tell me that there is something very wrong in our code. Like we need to call summarize with something different. Note to Kostas: The workspace and the table and the branch are the same as yesterday 😉 Thanks!

12 replies

XXata

•Created by Eusebio Trigo on 11/10/2023 in #help

Diagnosing Constraint Violation

Hi team! We are using a direct SQL to update rows in one of our databases. Some of them work, some of them throw a "constraint violation on transactions: link_m29iv801n56pd8oc7tk3q9nn2c" The requestId is "32375456-2959-9b47-925b-4f8fcbbaac14" This gotta be something wrong in our code, but I am failing to see what, so we need some support from you to see what's wrong.

22 replies

XXata

•Created by Eusebio Trigo on 10/6/2023 in #help

Objects with json columns after a summaries request provide properties as strings

Hi team, We are trying to run a summaries like: const summary = await xata.db.items.summarize({ columns: ["linkedTable.*"], summaries: {}, filter: { "anotherLinkedTable.someProperty": "someValue", }, }); the result comes as:

{
  "summaries": [
    {
      "linkedTable": {
        "prop1": null,
        "prop2": "{\"propName1\":\"propValue1\",\"propName2\":\"propValue2\",\"propName3\":null}",
        "xata": {
          "createdAt": "2023-09-20T15:16:53.245739Z",
          "updatedAt": "2023-10-03T20:24:26.884913Z",
          "version": 8
        }
      }
    }
  ]
}

{
  "summaries": [
    {
      "linkedTable": {
        "prop1": null,
        "prop2": "{\"propName1\":\"propValue1\",\"propName2\":\"propValue2\",\"propName3\":null}",
        "xata": {
          "createdAt": "2023-09-20T15:16:53.245739Z",
          "updatedAt": "2023-10-03T20:24:26.884913Z",
          "version": 8
        }
      }
    }
  ]
}

we would expect:

{
  "summaries": [
    {
      "linkedTable": {
        "prop1": null,
        "prop2": {
            "propName1":"propValue1",
            "propName2":"propValue2",
            "propName3":null
        },
        "xata": {
          "createdAt": "2023-09-20T15:16:53.245739Z",
          "updatedAt": "2023-10-03T20:24:26.884913Z",
          "version": 8
        }
      }
    }
  ]
}

{
  "summaries": [
    {
      "linkedTable": {
        "prop1": null,
        "prop2": {
            "propName1":"propValue1",
            "propName2":"propValue2",
            "propName3":null
        },
        "xata": {
          "createdAt": "2023-09-20T15:16:53.245739Z",
          "updatedAt": "2023-10-03T20:24:26.884913Z",
          "version": 8
        }
      }
    }
  ]
}

And we are getting the results but the objects we get from the linkedTable have their JSON columns coming back as a string. If we do a xata.db.linkedTable.read(linkedTable.id), we do get an object whose JSON columns are objects, and not a string.

6 replies

XXata

•Created by Eusebio Trigo on 9/26/2023 in #help

Import CSV from UI saves data in `main` branch but not in the selected branch

Hi team, We are testing the import and export through CSV, and found out that when using the UI the data exported from a branch it is imported directly into the main branch, but not in the selected branch.

12 replies

XXata

•Created by Eusebio Trigo on 8/21/2023 in #help

Migrations across workspaces

Hi Xata team! I'd like to know if it would be possible to do DDL migrations between different workspaces. I'll explain: we are aiming to have a different workspaces, one for production, another for development, etc. And each workspace contains databases whose branches are for multi-tenant purposes (one customer has one branch). I already have some code to update from the main database to any other branch in that database (I do what we can see in Xata's UI, but in code, but wanted to know if we can do it across workspaces. Thanks!!

21 replies

XXata

•Created by Eusebio Trigo on 3/21/2023 in #help

Exploring Migrations

Hi team, I'm exploring the migrations API so I can update all my branches with the latest schema (using branches in a multi-tenant approach). Some time ago I could use "xataApiClient.migrations.getBranchSchemaHistory" or https://xata.io/docs/api-reference/db/db_branch_name/schema/history#query-schema-history to query all the migrations that were applied to a branch. Now, i'm getting a 500 error in some branches and the list of changes in other branches. I have several RequestIDs if they come helpful for you. fbc58bc0-20d1-93f9-85f5-d8dde259cc2f 30629f5c-36d3-9a8f-9230-057503608b5f They all just come back as:

 status: 500,
  errors: [
    {
      message: 'Internal Error (RequestID: 30629f5c-36d3-9a8f-9230-057503608b5f)',
      status: 500
    }
  ],
  requestId: '30629f5c-36d3-9a8f-9230-057503608b5f',
  cause: undefined

From other branches I can output something like:


Database fc2c3fca-d71d-4657-b6d5-53da8d807de0 does not need to be updated
        Commit mig_cfscsaplsg2bukjkiiog was applied on 2023-02-24T14:48:11.396059Z
        Commit mig_cfeikv6481ilejqfj0ug was applied on 2023-02-03T15:38:36.792671Z

Thanks in advance for your help!

8 replies

XXata

•Created by Eusebio Trigo on 3/7/2023 in #help

Deno support for deps.ts

Hi team! I´m trying Deno, and following one of their best practices, they recommend moving all the dependencies with a url or a npm prefix to a deps.ts file: https://deno.land/[email protected]/examples/manage_dependencies Given that the "xata codegen" tool generates the client code and builds something like:

import { buildClient } from "npm:@xata.io/client@latest";
import type {
  BaseClientOptions,
  SchemaInference,
  XataRecord,
} from "npm:@xata.io/client@latest";

import { buildClient } from "npm:@xata.io/client@latest";
import type {
  BaseClientOptions,
  SchemaInference,
  XataRecord,
} from "npm:@xata.io/client@latest";

Is there a recommended way to handle it, as we should not change generated code...

3 replies

Gaming

Programming