X
Xata•7mo ago
agaitan026

how i can link two table like this

right now i got this sql query 
select MAX(products.id) as id,
COUNT(v.id) as variation_id,products.name as Nombre,c1.name as Categoria,
COUNT(products.category_id) as category_id,b.name as Marca,tax_rates.name as Impuesto,
SUM(COALESCE(vld.qty_available,0)) as Stockactual,MAX(v.sell_price_inc_tax) as PrecioVenta,
MIN(v.sell_price_inc_tax) as min_price,MAX(v.dpp_inc_tax) as max_purchase_price,
MIN(v.dpp_inc_tax) as min_purchase_price,products.sku as SKU,
products.product_custom_field1 as numerodeserie, products.product_custom_field2 as mac,
products.image,products.enable_stock as Estado,units.actual_name as unit,
products.enable_stock as enable_stock,products.created_at

FROM products

LEFT JOIN brands b 
ON products.brand_id  = b.id

JOIN units
ON products.unit_id = units.id

LEFT JOIN categories as c1 
ON products.category_id = c1.id

LEFT JOIN tax_rates
ON products.tax = tax_rates.id

JOIN variations as v
ON v.product_id = products.id

LEFT JOIN variation_location_details as vld
ON vld.variation_id = v.id

GROUP by products.id,products.name,c1.name,b.name,tax_rates.name,products.sku,products.product_custom_field1,products.product_custom_field2,products.image,products.enable_stock,units.actual_name,products.created_at

ORDER by products.id
select MAX(products.id) as id,
COUNT(v.id) as variation_id,products.name as Nombre,c1.name as Categoria,
COUNT(products.category_id) as category_id,b.name as Marca,tax_rates.name as Impuesto,
SUM(COALESCE(vld.qty_available,0)) as Stockactual,MAX(v.sell_price_inc_tax) as PrecioVenta,
MIN(v.sell_price_inc_tax) as min_price,MAX(v.dpp_inc_tax) as max_purchase_price,
MIN(v.dpp_inc_tax) as min_purchase_price,products.sku as SKU,
products.product_custom_field1 as numerodeserie, products.product_custom_field2 as mac,
products.image,products.enable_stock as Estado,units.actual_name as unit,
products.enable_stock as enable_stock,products.created_at

FROM products

LEFT JOIN brands b 
ON products.brand_id  = b.id

JOIN units
ON products.unit_id = units.id

LEFT JOIN categories as c1 
ON products.category_id = c1.id

LEFT JOIN tax_rates
ON products.tax = tax_rates.id

JOIN variations as v
ON v.product_id = products.id

LEFT JOIN variation_location_details as vld
ON vld.variation_id = v.id

GROUP by products.id,products.name,c1.name,b.name,tax_rates.name,products.sku,products.product_custom_field1,products.product_custom_field2,products.image,products.enable_stock,units.actual_name,products.created_at

ORDER by products.id
im trying to replicate in xata but its like too difficult i already did some of them except LEFT JOIN variation_location_details as vld ON vld.variation_id = v.id this one not sure how to do it if you see the attachment is the original variation_location_details tables and theres a product id that should be matched in product table attached too
No description
9 Replies
agaitan026
agaitan026OP•7mo ago
No description
agaitan026
agaitan026OP•7mo ago
with the others i dont have issue, but with this yes because for each product could be 1 or more records (this is how my current inventory system works, it add each transaction as a new records and i need to sum up all qty so i can show like productA actualstock = 10 units for example or productB actual stock = 20 whats the best approach for this? using rest for sure
kostas
kostas•7mo ago
This reads like a one-to-many relationship between each record in the products table, with potentially many records in the variation_location_details table. If you'd like to use Xata's REST API (without resulting to SQL statements) you can use a combination of links and the backwards reference operator (https://xata.io/docs/concepts/data-model#links-and-relationships) In practice this means: the product_id colum in variation_location_details instead of a regular string or int, would be a "link" column to the products table. You can then query the products table to get a summary count of the number of references to each of its records, from the variation_location_details table. Here's an example query and sample result output: 
const page = await xata.db.products
  .select([
    "xata_id",
    {
      name: "<-variation_location_details.product",
      columns: ["xata_id"],
      as: "product_references_in_variation_location_details",
      limit: 200,
      offset: 0,
    },
  ])
  .summarize({
  columns: ['xata_id'],
  summaries: {
    product_actual_stock: { count: "*" }
  }
});
const page = await xata.db.products
  .select([
    "xata_id",
    {
      name: "<-variation_location_details.product",
      columns: ["xata_id"],
      as: "product_references_in_variation_location_details",
      limit: 200,
      offset: 0,
    },
  ])
  .summarize({
  columns: ['xata_id'],
  summaries: {
    product_actual_stock: { count: "*" }
  }
});
That said, Xata's REST API needs the link column type to establish relationships between records. You'd need to make sure a valid product xata_id value is set in the link field "product" of variation_location_details. The alternative is to default to pure SQL, where the link column type is irrelevant as relations can be established on any column with joins.
agaitan026
agaitan026OP•7mo ago
How i can do that but using api rest? With json
kostas
kostas•7mo ago
You can run this REST API call which will return the raw record data, and then calculate the counts at the client side out of the response. It will return the variation_location_details records that link to each of the products, groupped in array under each product. It's just a matter of picking the length of that array, that is the count you're looking for per product. 
POST https://ws-123456.us-east-1.xata.sh/db/youdbname:main/tables/products/query

{
  "page": {
    "size": 1000,
    "offset": 0
  },
  "columns": [
    "xata_id",
    {
      "name": "<-variation_location_details.product",
      "columns": [
        "xata_id"
      ],
      "as": "product_references_in_variation_location_details",
      "limit": 200,
      "offset": 0
    }
  ]
}
POST https://ws-123456.us-east-1.xata.sh/db/youdbname:main/tables/products/query

{
  "page": {
    "size": 1000,
    "offset": 0
  },
  "columns": [
    "xata_id",
    {
      "name": "<-variation_location_details.product",
      "columns": [
        "xata_id"
      ],
      "as": "product_references_in_variation_location_details",
      "limit": 200,
      "offset": 0
    }
  ]
}
agaitan026
agaitan026OP•7mo ago
 {
  "meta": {
    "page": {
      "cursor": "dM1BisMwDIXh_RzjrT2Dk9n5Km0wwrGDimMXSymF0LuXJF2UQnf6hfh06VaINi4T3Al3UvI8wqwoNEc4nBdr_8PvjRqTci0-13AMY1TiLH_XVsclKAxCzctc5B0aDEjg8DryLabYYglRPBf_XYVB5pkVrrfWoKYkUeHsYzCQ2vTjR-Im-67bKtMR_R4H09nNSZkm2ZifZwAAAP__",
      "more": false,
      "size": 1000
    }
  },
  "records": [
    {
      "id": "1",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "1",
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:38:55.614973+00:00",
              "version": 1
            }
          },
          {
            "id": "6",
            "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",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "2",
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:04.164232+00:00",
              "version": 1
            }
          },
          {
            "id": "7",
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:16.405594+00:00",
              "version": 1
            }
          }
        ]
      },
      "xata": {
        "createdAt": "2024-05-24T16:26:48.597836Z",
        "updatedAt": "2024-06-03T16:29:08.960135Z",
        "version": 11
      }
    }
  ]
} 
 {
  "meta": {
    "page": {
      "cursor": "dM1BisMwDIXh_RzjrT2Dk9n5Km0wwrGDimMXSymF0LuXJF2UQnf6hfh06VaINi4T3Al3UvI8wqwoNEc4nBdr_8PvjRqTci0-13AMY1TiLH_XVsclKAxCzctc5B0aDEjg8DryLabYYglRPBf_XYVB5pkVrrfWoKYkUeHsYzCQ2vTjR-Im-67bKtMR_R4H09nNSZkm2ZifZwAAAP__",
      "more": false,
      "size": 1000
    }
  },
  "records": [
    {
      "id": "1",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "1",
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:38:55.614973+00:00",
              "version": 1
            }
          },
          {
            "id": "6",
            "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",
      "product_references_in_variation_location_details": {
        "records": [
          {
            "id": "2",
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:04.164232+00:00",
              "version": 1
            }
          },
          {
            "id": "7",
            "xata": {
              "createdAt": "2024-05-24T16:27:22.855691+00:00",
              "updatedAt": "2024-06-15T13:39:16.405594+00:00",
              "version": 1
            }
          }
        ]
      },
      "xata": {
        "createdAt": "2024-05-24T16:26:48.597836Z",
        "updatedAt": "2024-06-03T16:29:08.960135Z",
        "version": 11
      }
    }
  ]
}
i got this
agaitan026
agaitan026OP•7mo ago
in theory i should have count of product 1 = 2 and product_id 2 = 2 also
No description
agaitan026
agaitan026OP•7mo ago
got it then i just need to count the array for each id, works good, thank you very much
kostas
kostas•7mo ago
Unfortunately the REST API does not support summarizing on records returned from reverse link lookups so you can fall back to SQL (over HTTP) for that part. Alternatively, you can do the summary calculations at the client side, after retrieving all the records with the <- operator.
Want results from more Discord servers?
Add your server