[PS1.7 export products with images] Query where images path are included. #PS17

query.sql

SELECT 
p.id_product AS 'ID du produit',
p.active AS 'Actif (0/1)',
pl.name AS 'Nom',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Catégories (x,y,z...)',
p.price AS 'Prix TTC ou HT à choix',
p.id_tax_rules_group AS 'ID règle de taxes',
p.wholesale_price AS 'Prix d_achat',
p.on_sale AS 'En soldes (0/1)',
IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Montant de la remise',
IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Pourcentage de réduction',
pr.from AS 'Réduction de (aaaa-mm-jj)',
pr.to AS 'Réduction à (aaaa-mm-jj)',
p.reference AS 'Référence',
p.supplier_reference AS 'Référence fournisseur',
ps.name AS 'Fournisseurs',
pm.name AS 'Marque',
p.ean13 AS 'EAN-13',
p.upc AS 'UPC',
p.ecotax AS 'Eco-participation',
p.width AS 'Largeur',
p.height AS 'Hauteur',
p.depth AS 'Profondeur',
p.weight AS 'Poids',
pl.delivery_in_stock AS 'Délai de livraison_produits en stock :',
pl.delivery_out_stock AS 'Délai de livraison_produits épuisés_commande autorisée:',
sa.quantity AS 'Quantité',
p.minimal_quantity AS 'Quantité minimale',
2 AS 'N.Stock bas',
0 AS 'Email quand la quantité est en dessous de ce niveau',
p.visibility AS 'Visibilité',
p.additional_shipping_cost AS 'Frais de port supplémentaire',
p.unity AS 'Unité pour le prix unitaire',
p.unit_price_ratio AS 'Prix unitaire',
pl.description_short AS 'Récapitulatif',
pl.description AS 'Description',
IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Mot_clés (x,y,z...)',
pl.meta_title AS 'Balise titre',
pl.meta_keywords AS 'Meta mots_clés',
pl.meta_description AS 'Meta description',
pl.link_rewrite AS 'URL réécrite',
pl.available_now AS 'Libellé si en stock',
pl.available_later AS 'Libellé quand précommande activée',
p.available_for_order AS 'Disponible à la commande (0 = Non, 1 = Oui)',
p.available_date AS 'Date de disponibilité du produit',
p.date_add  AS 'Date d_ajout du produit',
p.show_price AS 'Afficher le prix (0 = Non, 1 = Oui)',
--  AS image_url,
GROUP_CONCAT(DISTINCT(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')) SEPARATOR ', ') AS 'URL des images (x,y,z, etc.)',
'' AS 'Textes alternatif des images (x,y,z...)',
1 AS 'Supprimer les images existantes (0 = Non, 1 = Oui)',
GROUP_CONCAT(DISTINCT(CONCAT((fl.name),
':',
(fvl.value),
':0')) SEPARATOR ',') AS 'Caractéristique (Nom:Valeur:Position:Personnalisé)',
0 AS 'Disponible en ligne uniquement (0 = Non, 1 = Oui)',
p.condition AS 'État'

FROM ps_product p
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute pa 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_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_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)
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;