Query MySQL per esportare tutte le combinazioni prodotto con PrestaShop testato su versione 1.6.0.x e 1.6.1.x
Una volta aperto PhpMyAdmin e aver selezionato il database, clicchiamo sul tab SQL. In questa sezione possiamo inserire la seguente query per selezionare la lista di tutti i prodotti con le combinazioni esplose:
SELECT p.id_product, pam.name as nome_marca, p.reference, pl.name as nome_prodotto, GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, pa.reference as reference_combination, pa.ean13, p.price as price_a, pap.price as price_b, pap.price + p.price AS prezzo_combinazione, pq.quantity FROM ps_product_attribute pa LEFT JOIN ps_product p ON (p.id_product = pa.id_product) LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute) LEFT JOIN ps_manufacturer pam ON (p.id_manufacturer = pam.id_manufacturer) LEFT JOIN ps_product_attribute pap ON ( pap.id_product_attribute = pa.id_product_attribute ) WHERE pl.id_lang = 1 AND pal.id_lang = 1 GROUP BY pa.reference ORDER BY p.id_product, pac.id_attribute
E’ possibile poi salvare il risultato in un file CSV o XLS cliccando sul tasto Esporta presente nel menù Operazioni sui risultati della query, sotto la lista dei prodotti appena printati. Questa query restituisce i seguenti valori:
ID prodotto, Nome marca, Referenza prodotto, Nome prodotto, Combinazione, Referenza combinazione, EAN13 combinazione, Prezzo, Quantità
Ovviamente è possibile personalizzare la query per restituire campi aggiuntivi. Ecco una versione più completa.
SELECT
p.id_product,
p.active,
pl.name,
GROUP_CONCAT(DISTINCT (cl.name)
SEPARATOR ',') as categories,
p.price,
p.id_tax_rules_group,
p.wholesale_price,
p.reference,
p.supplier_reference,
p.id_supplier,
p.id_manufacturer,
p.ean13,
p.upc,
p.ecotax,
p.weight,
p.quantity,
pl.description_short,
pl.description,
pl.meta_title,
pl.meta_keywords,
pl.meta_description,
pl.link_rewrite,
pl.available_now,
pl.available_later,
p.available_for_order,
p.date_add,
p.show_price,
concat('http://',
ifnull(shop_domain.value, 'domain'),
'/img/p/',
if(CHAR_LENGTH(pi.id_image) >= 5,
concat(SUBSTRING(pi.id_image from - 5 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 4,
concat(SUBSTRING(pi.id_image from - 4 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 3,
concat(SUBSTRING(pi.id_image from - 3 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 2,
concat(SUBSTRING(pi.id_image from - 2 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 1,
concat(SUBSTRING(pi.id_image from - 1 FOR 1),
'/'),
''),
pi.id_image,
'.jpg') as image_url,
p.online_only,
p.condition,
p.id_shop_default
FROM
ps_product p
LEFT JOIN
ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN
ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN
ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN
ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN
ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN
ps_image pi ON p.id_product = pi.id_product
LEFT JOIN
ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN'
WHERE
pl.id_lang = (SELECT id_lang FROM ps_lang WHERE active = '1' ORDER BY id_lang ASC LIMIT 1)
AND cl.id_lang = pl.id_lang
AND p.id_shop_default = (SELECT id_shop FROM ps_shop WHERE active = '1' ORDER BY id_shop ASC LIMIT 1)
AND c.id_shop_default = p.id_shop_default
GROUP BY p.id_product
