ASTUCE: Requete SQL exportation produits CSV toutes les images et plus

[Baba deCode]

Bonjour,
Voici une requete SQL export produit que j'utilise pour export csv de tout ce que j'ai besoin... Je n'en ai pas trouvé pour exporter toutes les images produits alors j'en ai crée une.
Fonctionne sur prestashop 1.6.1
Enjoy

SELECT 
p.`id_product` AS `ID`,
b.name as `Nom`,`reference`,
cl.`id_category` AS `ID Cat defaut`,
cl.`name` AS `Cat defaut`,
GROUP_CONCAT(DISTINCT(c.id_category) SEPARATOR ",") as categories,
GROUP_CONCAT(DISTINCT(case
    when length(im.`id_image`)=6 then
     concat('http://www.monsite.com/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http://www.MONSITE.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http://www.MONSITE.comm/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end) SEPARATOR ",") as "Images",
sa.`price`,
ml.meta_title AS 'Marque',
ml.id_manufacturer AS 'Marque ID',
p.price as price,
sa.active as active,
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
sav.`quantity` AS `quantite`,
p.visibility, 
p.indexed, 
pl.description_short, 
pl.description, 
pl.meta_title, 
pl.meta_keywords, 
pl.meta_description,
MAX(image_shop.id_image) id_image,

concat(pl.`link_rewrite`,'-',p.`id_product`,'.html') as "ProductURL google",
case
    when length(im.`id_image`)=6 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http://www.MONSITE.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http://www.MONSITE.comm/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end as "ImgURL_1"
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_lang` b ON (b.`id_product` = p.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = p.id_shop_default) 
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) 
LEFT JOIN `ps_manufacturer_lang` ml ON (p.id_manufacturer = ml.id_manufacturer)
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop_group = 0  AND sav.id_shop = 1 )  JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = p.id_shop_default) 
LEFT JOIN `ps_shop` shop ON (shop.id_shop = p.id_shop_default) 
LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = p.id_shop_default) 
LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
LEFT JOIN `ps_image` im on im.`id_product`= p.`id_product`

WHERE 1 AND pl.id_lang = 1 
GROUP BY sa.id_product 
ORDER BY p.`id_product` ASC

Edited August 14, 2015 by Alexandre-KM (see edit history)

[Soyons Solidaire]

 

Bonjour,

Voici une requete SQL export produit que j'utilise pour export csv de tout ce que j'ai besoin... Je n'en ai pas trouvé pour exporter toutes les images produits alors j'en ai crée une.

Fonctionne sur prestashop 1.6.1

Enjoy

SELECT 
p.`id_product` AS `ID`,
b.name as `Nom`,`reference`,
cl.`id_category` AS `ID Cat defaut`,
cl.`name` AS `Cat defaut`,
GROUP_CONCAT(DISTINCT(c.id_category) SEPARATOR ",") as categories,
GROUP_CONCAT(DISTINCT(case
    when length(im.`id_image`)=6 then
     concat('http://www.monsite.com/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http://www.MONSITE.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http://www.MONSITE.comm/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end) SEPARATOR ",") as "Images",
sa.`price`,
ml.meta_title AS 'Marque',
ml.id_manufacturer AS 'Marque ID',
p.price as price,
sa.active as active,
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
sav.`quantity` AS `quantite`,
p.visibility, 
p.indexed, 
pl.description_short, 
pl.description, 
pl.meta_title, 
pl.meta_keywords, 
pl.meta_description,
MAX(image_shop.id_image) id_image,

concat(pl.`link_rewrite`,'-',p.`id_product`,'.html') as "ProductURL google",
case
    when length(im.`id_image`)=6 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('http://www.MONSITE.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http://www.MONSITE.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http://www.MONSITE.comm/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end as "ImgURL_1"
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_lang` b ON (b.`id_product` = p.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = p.id_shop_default) 
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) 
LEFT JOIN `ps_manufacturer_lang` ml ON (p.id_manufacturer = ml.id_manufacturer)
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop_group = 0  AND sav.id_shop = 1 )  JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = p.id_shop_default) 
LEFT JOIN `ps_shop` shop ON (shop.id_shop = p.id_shop_default) 
LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = p.id_shop_default) 
LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
LEFT JOIN `ps_image` im on im.`id_product`= p.`id_product`

WHERE 1 AND pl.id_lang = 1 
GROUP BY sa.id_product 
ORDER BY p.`id_product` ASC

Bonjour, Merci pour ce partage s.t.p le code il faut le mettre ou ? www.MONSITE.com Remplacer uniquement cette adresse ? Amitiés

Edited August 14, 2015 by Soyons Solidaires (see edit history)

[Baba deCode]

bonjour,

Dans phpmyadmin onglet SQL, dans le back office de prestashop a SQL manager je n'ai pas essayé mais ca devrait marcher... Comme tu l as bien compris il faut remplacer l'url par ton site.

 

CDT

[Soyons Solidaire]

bonjour,

Dans phpmyadmin onglet SQL, dans le back office de prestashop a SQL manager je n'ai pas essayé mais ca devrait marcher... Comme tu l as bien compris il faut remplacer l'url par ton site.

 

CDT

Ok Merci du retour ! Bonne soirée

[Autourdupc]

Bonjour,

 

Sous PS1.6.0.9, j'ai l'erreur suivante :

 

1. Lorsque plusieurs tables sont utilisées, chaque attribut doit être référencé à l'une de ces tables.

 

Pouvez-vous m'aider ?

 

Cordialement,

Laurent.

[Comleweb]

Merci pour le partage, c'est exactement ce dont j'avais besoin.

 

 

[houba_houbi]

bonjour merci pour cette requete qui fonctionne

Comment pourrais t'on avoir la description du produit également dans ps_product_lang ?