Saltar al contenido
Codifíca.me | Desarrollo web | Programación

Consultar todos los productos e imágenes en Prestashop | MySQL

8 febrero, 2021
prestashop

Hola,

En esta entrada vamos a ver cómo consultar todas las imágenes y todos los datos de todos los productos que tenemos en prestashop.

A continuación dejo la query con la que puedes consultar todos los datos.

Está query la he lanzado y probado sobre una versión de PrestaShop 1.7.

SELECT p.id_product, p.active, pl.name AS 'Name',
     GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z…)',   
     p.ean13 AS 'EAN13',
     p.upc AS 'UPC',
     p.ecotax AS 'Ecotax',
     p.width AS 'Width',
     p.height AS 'Height',
     p.depth AS 'Depth',
     p.weight AS 'Weight',
     sa.quantity as 'Quantity',
     p.minimal_quantity AS 'Minimal quantity',
     'both' AS 'Visibility',
     p.additional_shipping_cost AS 'Additional shipping cost',
     p.unity AS 'Unity',
     p.unit_price_ratio AS 'Unit price',
     pl.description_short AS 'Short description',
     pl.description AS 'Description',
     IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z…)',
     pl.meta_title AS 'Meta title',
     pl.meta_keywords AS 'Meta keywords',
     pl.meta_description AS 'Meta description',
     pl.link_rewrite AS 'URL rewritten',
     pl.available_now AS 'Text when in stock',
     pl.available_later AS 'Text when backorder allowed',
     p.available_for_order AS 'Available for order (0 = No, 1 = Yes)',
     '' AS 'Product available date',
     p.date_add 'Product creation date',
     p.show_price AS 'Show price (0 = No, 1 = Yes)',
 CONCAT('https://',
         -- get the shop domain
         IFNULL(conf.value, 'undefined_domain'),
         -- the path to the pictures folder
         '/img/p/',
         -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
         -- assuming we have smaller image id than 100'000 ;)
         IF(CHAR_LENGTH(pi.id_image) >= 5, 
             -- if we have 5 digits for the image id
             CONCAT(
                 -- take the first digit
                 SUBSTRING(pi.id_image, -5, 1),
                 -- add a slash
                 '/'),
             ''),
         -- repeat for the next digits
         IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
         IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
         if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
         IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
         -- add the image id
         pi.id_image,
         -- put the image extension
         '.jpg') as image_url,
 CONCAT('https://',
         -- get the shop domain
         IFNULL(conf.value, 'undefined_domain'),
         -- the path to the pictures folder
         '/img/p/',
         -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
         -- assuming we have smaller image id than 100'000 ;)
         IF(CHAR_LENGTH(pi2.id_image) >= 5, 
             -- if we have 5 digits for the image id
             CONCAT(
                 -- take the first digit
                 SUBSTRING(pi2.id_image, -5, 1),
                 -- add a slash
                 '/'),
             ''),
         -- repeat for the next digits
         IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
         IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
         if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
         IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
         -- add the image id
         pi2.id_image,
         -- put the image extension
         '.jpg') as image_url2,
     0 AS 'Delete existing images (0 = No, 1 = Yes)',
     GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Value:Position)',
     p.online_only AS 'Available online only (0 = No, 1 = Yes)',
     p.condition AS 'Cond',
     0 AS 'Customizable (0 = No, 1 = Yes)',
     0 AS 'Uploadable files (0 = No, 1 = Yes)',
     0 AS 'Text fields (0 = No, 1 = Yes)',
     p.out_of_stock as 'Out of stock',
     '1' AS 'ID',
     null AS 'Action when out of stock',
     null AS 'Depends on stock',
     null AS 'Warehouse'
 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_specific_price pr ON(p.id_product = pr.id_product)
 LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
 LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
 LEFT JOIN ps_image pi ON(p.id_product = pi.id_product and pi.cover = 1)
 LEFT JOIN ps_image pi2 ON(p.id_product = pi2.id_product and pi2.position = 2)
 LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
 LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
 LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
 LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
 LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
 LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
 LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
 LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
 LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)
 WHERE pl.id_lang = 1
 AND cl.id_lang = 1
 GROUP BY p.id_product;

La tendrías que lanzar desde MySQL o desde consola y te debe de mostrar un registro por cada uno de los productos que tengas así como las imágenes que tienes.