SQL query to create new meta descriptions in spreadsheet

[Chris2022]

Hey Everyone,

I building a custom SQL query of a product name with id_product, id_lang, id_manufacturer, category, meta_description, brand name and multiple feature ids. This SQL query shows no result.

The goal is to creat a new meta description in the spreadsheet and import it to the database. In php, the query would be easier, but I have zero knowledge about it.

Here's my query:

SELECT pl.id_product, pl.id_lang, ml.id_manufacturer, p.active, pl.name as name_product, fp.id_feature as name_attribute, cl.name as category, m.name as brand, pl.meta_description
FROM pr_product p
LEFT JOIN pr_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN pr_category_lang cl ON (cl.id_category = p.id_category_default and cl.id_lang = pl.id_lang)
LEFT JOIN pr_lang l on (l.id_lang = pl.id_lang)
LEFT JOIN pr_manufacturer_lang ml on (l.id_lang = pl.id_lang and l.id_lang = ml.id_lang)
LEFT JOIN pr_manufacturer m on (ml.id_manufacturer = m.id_manufacturer)
LEFT JOIN pr_feature_product fp on (pl.id_product = fp.id_product)
Where l.active = 1 AND pl.id_lang = 2 AND cl.name = 9 and fp.id_feature = 2 AND fp.id_feature = 3 AND fp.id_feature = 35 AND fp.id_feature = 39
Order by p.id_product, pl.id_lang, ml.id_manufacturer, fp.id_feature

 

[ps8modules]

Hi.
Wouldn't it be better to write here what the meta description should be made of?
From the product name?
From the short description of the product?

[ps8modules]

/* Because then there are simple sql.
E.g.*/
UPDATE ps_product_lang SET meta_description = SUBSTR(CONCAT(name, ' - ', description_short), 1, 150);

 

[Chris2022]

Thanks for the reply

I would like to get these columns from the query:

 

So far, I was able to build such a query as a test. There are 15 records instead of one, and the features are 0-1 instead of the text

SELECT pl.id_product, cl.id_category, pl.id_lang, pl.name as product_name, cl.name as category, pf.id_feature = 13, pf.id_feature = 35, pf.id_feature = 2, pf.id_feature = 39, pf.id_feature = 3, m.name as brand, pl.meta_description, p.active
FROM pr_product p
LEFT JOIN pr_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN pr_category_lang cl ON cl.id_category = p.id_category_default and cl.id_lang = pl.id_lang
Left Join pr_lang l on l.id_lang = pl.id_lang
LEFT JOIN pr_manufacturer_lang ml on (l.id_lang = pl.id_lang and l.id_lang = ml.id_lang)
LEFT JOIN pr_manufacturer m on (ml.id_manufacturer = m.id_manufacturer)
LEFT JOIN pr_feature_product pf ON p.id_product = pf.id_product 
LEFT JOIN pr_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 4)
LEFT JOIN pr_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 4)
LEFT JOIN pr_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 4)
INNER JOIN pr_feature_shop feature_shop
ON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1)
where pl.id_lang = 4 and pf.id_product = 4
order by p.id_product, pl.id_lang

 

Thanks for the Update query, will be usefull. However, I have to show the columns in the workbook for approval to the client

 

 

 

Edited October 27, 2022 by Chris2022 (see edit history)

[ps8modules]

Ok, sample:

SELECT 
pl.id_product /* ADD */as 'id_product', 
cl.id_category, 
pl.id_lang, 
pl.name as product_name, 
cl.name as category, 
/* NEW */
(
    SELECT x.value FROM pr_feature_value_lang x
    LEFT JOIN pr_feature_product y ON (y.id_product = id_product)
    WHERE x.id_lang = 4 AND y.id_feature = 13
) AS 'Surface Safety (id_feature = 13)', 
(
    SELECT x.value FROM pr_feature_value_lang x
    LEFT JOIN pr_feature_product y ON (y.id_product = id_product)
    WHERE x.id_lang = 4 AND y.id_feature = 35
) AS 'Kee Safety (id_feature = 35)', 
/*pf.id_feature = 13,
pf.id_feature = 35, 
pf.id_feature = 2, 
pf.id_feature = 39, 
pf.id_feature = 3, */
m.name as brand, 
pl.meta_description, 
p.active
FROM pr_product p
LEFT JOIN pr_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN pr_category_lang cl ON cl.id_category = p.id_category_default and cl.id_lang = pl.id_lang
Left Join pr_lang l on l.id_lang = pl.id_lang
LEFT JOIN pr_manufacturer_lang ml on (l.id_lang = pl.id_lang and l.id_lang = ml.id_lang)
LEFT JOIN pr_manufacturer m on (ml.id_manufacturer = m.id_manufacturer)
/*LEFT JOIN pr_feature_product pf ON p.id_product = pf.id_product */
/*LEFT JOIN pr_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 4)*/
/*LEFT JOIN pr_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 4)*/
/*LEFT JOIN pr_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 4)*/
/*INNER JOIN pr_feature_shop feature_shopON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1)*/
where pl.id_lang = 4 and pf.id_product = 4
order by p.id_product, pl.id_lang

 

[Chris2022]

Thanks. Now I understand how to build subqueries. Only for a week I have been learning about SQL and the names of tables and columns from Presta

Presta shows such an error:

 

When build query without subqueries, with only one product id and one feature id, it displays correctly:

SELECT pl.id_product, cl.id_category, pl.id_lang, pl.name as product_name, cl.name as category, fvl.value, m.name as brand, pl.meta_description, p.active
FROM pr_product p
LEFT JOIN pr_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN pr_category_lang cl ON cl.id_category = p.id_category_default and cl.id_lang = pl.id_lang
Left Join pr_lang l on l.id_lang = pl.id_lang
LEFT JOIN pr_manufacturer_lang ml on (l.id_lang = pl.id_lang and l.id_lang = ml.id_lang)
LEFT JOIN pr_manufacturer m on (ml.id_manufacturer = m.id_manufacturer)
LEFT JOIN pr_feature_product pf ON p.id_product = pf.id_product 
LEFT JOIN pr_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 4)
LEFT JOIN pr_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 4)
LEFT JOIN pr_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 4)
INNER JOIN pr_feature_shop feature_shop
ON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1)
where pl.id_lang = 4 and pf.id_product = 4 and pf.id_feature = 13
order by p.id_product, pl.id_lang

 

I assume that it is about the correct add of the id shop