Xata•10mo ago

summarize columns linked table

"records": [
    {
      "id": "1",
      "name": "MW305R Router Inalámbrico N de 300Mbps",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "1",
            "qty_available": 11,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:38:55.614973+00:00",
              "version": 1
            }
          },
          {
            "id": "6",
            "qty_available": 5,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:09.60911+00:00",
              "version": 1
            }
          }
        ]
      },
      "xata": {
        "createdAt": "2024-05-24T16:26:48.597836Z",
        "updatedAt": "2024-06-03T16:28:37.242412Z",
        "version": 13
      }
    },
    {
      "id": "2",
      "name": "MS105G Switch de escritorio de 5 puertos 10/100 / 1,000 Mbps",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "2",
            "qty_available": 11,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:04.164232+00:00",
              "version": 1
            }
          },
          {
            "id": "7",
            "qty_available": 0,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T14:18:49.988908+00:00",
              "version": 5
            }
          }
        ]
      },
      "xata": {
        "createdAt": "2024-05-24T16:26:48.597836Z",
        "updatedAt": "2024-06-03T16:29:08.960135Z",
        "version": 11
      }
    }
  ]
}

"records": [
    {
      "id": "1",
      "name": "MW305R Router Inalámbrico N de 300Mbps",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "1",
            "qty_available": 11,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:38:55.614973+00:00",
              "version": 1
            }
          },
          {
            "id": "6",
            "qty_available": 5,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:09.60911+00:00",
              "version": 1
            }
          }
        ]
      },
      "xata": {
        "createdAt": "2024-05-24T16:26:48.597836Z",
        "updatedAt": "2024-06-03T16:28:37.242412Z",
        "version": 13
      }
    },
    {
      "id": "2",
      "name": "MS105G Switch de escritorio de 5 puertos 10/100 / 1,000 Mbps",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "2",
            "qty_available": 11,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:04.164232+00:00",
              "version": 1
            }
          },
          {
            "id": "7",
            "qty_available": 0,
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T14:18:49.988908+00:00",
              "version": 5
            }
          }
        ]
      },
      "xata": {
        "createdAt": "2024-05-24T16:26:48.597836Z",
        "updatedAt": "2024-06-03T16:29:08.960135Z",
        "version": 11
      }
    }
  ]
}

i got the following response i need to sum up qty_available for each product, how i can do it?

2 Replies

agaitan026OP•10mo ago

for example product MW305R Router Inalámbrico N de 300Mbps" should have qty_available 11 + 5 = 16 and MS105G Switch de escritorio de 5 puertos 10/100 / 1,000 Mbps qty_available = 11 + 0 = 11 current request

{
  "page": {
    "size": 1000,
    "offset": 0
  },
  "columns": [
     "name",
    "xata_id",
    {
      "name": "<-variation_location_details.product",
      "columns": [
        "xata_id",
        "qty_available"
      ],
      "as": "product_references_in_variation_location_details",
      "limit": 200,
      "offset": 0
    }
  ]
}

{
  "page": {
    "size": 1000,
    "offset": 0
  },
  "columns": [
     "name",
    "xata_id",
    {
      "name": "<-variation_location_details.product",
      "columns": [
        "xata_id",
        "qty_available"
      ],
      "as": "product_references_in_variation_location_details",
      "limit": 200,
      "offset": 0
    }
  ]
}

kostas•10mo ago

Backwards link traversing can be used to retrieve records but not for applying calculations or filtering on them. The summarize API cannot be used with the reverse link operator <-. Instead, you can use SQL (https://xata.io/docs/sdk/sql/overview) to perform this calculation:

SELECT SUM(qty_available),products.name FROM "variation_location_details" JOIN "products" ON variation_location_details.product = products.id GROUP BY products.name LIMIT 200;

SELECT SUM(qty_available),products.name FROM "variation_location_details" JOIN "products" ON variation_location_details.product = products.id GROUP BY products.name LIMIT 200;

SQL over HTTP

How to access Xata using SQL directly over HTTP

Gaming

Programming

summarize columns linked table

Did you find this page helpful?