(Solved) SQL Query to get full product path ( Friendly url)

[siomosp]

Hello!

I want to use SQL Query to get a full path of a product 

The following is not working if the default category is a subcategory

Can someone help ?

Actual url

https://www.stickit.gr/en/Posters/series/nothing-worth-having-comes-easy-aesthetic-poster

Query result (Missing "Posters" from url)

https://www.stickit.gr/en/series/nothing-worth-having-comes-easy-aesthetic-poster

I cannot write   my sql here, see the screenshot, the code is blocked from prestahop!
 

Edited October 7, 2022 by siomosp (see edit history)

[siomosp]

Well,

I had to do a lot of research for my poor mysql knowledge.

But this query works.

It generates product url for the given shop, product id ,  language

SET @site = "https://www.stickit.gr/el";
SET @id_lang = 2;
SET @id_prod = 18077;
SELECT CONCAT_WS("/",@site,GROUP_CONCAT(ps_category_lang.link_rewrite ORDER BY ps_category.level_depth ASC SEPARATOR "/"),
(SELECT ps_product_lang.link_rewrite
FROM  ps_product_lang
INNER JOIN ps_product ON ps_product.id_product = ps_product_lang.id_product
INNER JOIN ps_category_lang ON ps_category_lang.id_category = ps_product.id_category_default
WHERE ps_product_lang.id_lang = @id_lang
AND ps_category_lang.id_lang = @id_lang
AND ps_product.id_product = @id_prod)) as product


FROM ps_category_lang 
INNER JOIN ps_category on ps_category_lang.id_category = ps_category.id_category
WHERE 
ps_category.nleft <= (SELECT ps_category.nleft
FROM ps_category
JOIN ps_product ON ps_product.id_category_default = ps_category.id_category
WHERE  ps_product.id_product = @id_prod)

AND
ps_category.nright >= (SELECT  ps_category.nright
FROM ps_category
JOIN ps_product ON ps_product.id_category_default = ps_category.id_category
WHERE  ps_product.id_product = @id_prod)

AND ps_category_lang.id_lang = @id_lang
AND ps_category.id_category > @id_lang

Result

https://www.stickit.gr/el/pinakes-se-kamva/pinakes-zografikis/pinakas-zografikis-elia

Edited October 8, 2022 by siomosp
Added ORDER BY ps_category.level_depth ASC (see edit history)