Jump to content

Edit History

siomosp

siomosp


Added ORDER BY ps_category.level_depth ASC

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

siomosp

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 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

siomosp

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 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

 

siomosp

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 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
#INNER JOIN ps_product ON ps_product.id_category_default = ps_category_lang.id_category
#INNER JOIN ps_product_lang ON ps_product.id_product = ps_product.id_product
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

 

siomosp

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 

SELECT CONCAT_WS("/","https://www.stickit.gr/el",GROUP_CONCAT(ps_category_lang.link_rewrite 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 = 2
AND ps_category_lang.id_lang = 2
AND ps_product.id_product = 192)) as product


FROM ps_category_lang 
INNER JOIN ps_category on ps_category_lang.id_category = ps_category.id_category
#INNER JOIN ps_product ON ps_product.id_category_default = ps_category_lang.id_category
#INNER JOIN ps_product_lang ON ps_product.id_product = ps_product.id_product
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 = 192)

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 = 192)

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

 

×
×
  • Create New...