When working on Issue 9010: Integration with vue storefront - Tryton issue tracker, I need to create and search on SKU for template. So the SKU for a product is simple as it is its code. But for template with multiple variant, usually it is the common part of all the variant. So if I have variants like this: PROD001, PROD-002, PROD-080, the SKU for the template will be PROD-0.
I used to use os.path.commonprefix to get it but now to implete GET /api/stock/list I need to be able to search on template sku.
So I wrote this SQL query:
WITH RECURSIVE s(n, id) AS (
SELECT 1, p.id FROM product_product AS p
UNION
SELECT n + 1, p.id FROM s, product_product AS p WHERE n < LENGTH(p.code)
), c(t, n) AS (
SELECT p.template, COUNT(*) FROM product_product AS p GROUP BY p.template HAVING COUNT(*) > 1
)
SELECT DISTINCT p.template, FIRST_VALUE(SUBSTRING(p.code FROM 0 FOR s.n + 1)) OVER (PARTITION BY p.template ORDER BY s.n DESC)
FROM product_product AS p
JOIN s ON p.id = s.id
JOIN c ON c.t = p.template
WHERE code IS NOT NULL AND code != ''
GROUP BY 1, SUBSTRING(p.code FROM 0 FOR s.n + 1), s.n, c.n
HAVING COUNT(*) = c.n
ORDER BY 1, 2;
I’m wondering if anyone find any optimization or better way to do it.
The query needs to be used for the getter and searcher of the SKU function field so it must return the couple template id and sku.
Because SKU is public so it may be needed at some point in time to change the product but keep the external reference.
Also SKU are part of the URL so it is better to have something meaningful for referencing.
And finally I need something unique across variants and templates.
I came with the solution to add code on template which become the prefix for the variant. The code of the variant is synchronized each time a template or a product is modified by concatenating the template code (as prefix) and the suffix code.