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