14 Replies
and left join like this
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
This capability is provided with the Kysely integration, you can find more details here: https://xata.io/docs/sdk/get#multiple-relationship-records-per-table. It uses the SQL over HTTP (https://xata.io/docs/sdk/sql/overview) implementation underneath.
Xata's REST API does not have the notion of the traditional SQL JOIN. Instead it uses a relation model based on the link column type, which in a sense is a JOIN on the id column between tables. The left/inner/right join part is covered by selecting certain columns when querying a record https://xata.io/docs/sdk/get#selecting-columns-from-the-linked-tables
The link value must be "prefilled" (written) within your records, so relations cannot be formed at just the query time.
So the join is with linked table but what about left join?
Linked table too?
Yes, as there is no "join" with the REST API other than the link column type. The way to approach this is via a query selecting certain columns from the target table or its linked ones https://xata.io/docs/sdk/get#selecting-columns-from-the-linked-tables
In my example i need to create 6 linked columns one on each of those tables i will try and let you know, thank you
JOIN variations as v
ON v.product_id = products.id if i have this, where i should create linked column? on table variations or products?
products right?
Yes, link columns should be added on the table you are querying directly. So if your query is against "products", that is the table that should have link columns to other tables.
i tried that but its empty
The links between each record must be created explicitly, it is not automatically filled
Each record can be linked to another record in a target table, by its id in the target table
oh so manually , what about i got like 1k records
It would need to be part of your application logic to create records linking to others
The alternative is to use the Kysely integration or the SQL over HTTP (xata.sql
STATEMENT sdk call) to do actual SQL joinsno i cant, i should use rest
oh so this is most for new apps, if i got already a table with 1k or more i should do it manually
Note that the SQL over HTTP endpoint (https://xata.io/docs/sdk/sql/overview) is also a rest endpoint. You can call it from any http/s client.
But if you're looking to use the baked-in link column type, then yes each linking record id must be explicitly set.
oh yes, that works awesome, i may use a mix between thos sql over http and the regular way
awesome xata 🙂 im testing it but i think i will stay with it
Glad to read this approach will work for you!