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
}
}
]
}
2 Replies
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
}
]
}
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