Jump to content
Fabiteby

Récupérer la bonne image principal lors d'un export des produits via SQL

Recommended Posts

Bonjour,

je voudrais exporter mes produits d'un prestashop 1.7.0 pour les réimporter dans un prestashop 1.7.3.0.

Je suis parvenu à récupérer en SQL les données qui m'intéressent.
Malheureusement, j'ai plusieurs images par produits et pour de nombreux produits, l'image principale n'est plus la bonne.

Dans le résultat de ma requête SQL, il faudrait que mon image principale soit en 1ère position, mais comment faire ?
Je sèche complètement !

Voici ma requête complète pour  récupérer mes produits :

SELECT
p.id_product AS "Product ID",
p.active AS "Active (0/1)",
la.name AS "Name",
GROUP_CONCAT(DISTINCT (cp.id_category) SEPARATOR '|') AS "Categories (x|y|z)",
p.price AS "Price Tax inclued",
p.id_tax_rules_group AS "Tax rules ID",
p.on_sale AS "On sale (0/1)",
p.reference AS "Reference",
p.ean13 AS "Ean13",
p.minimal_quantity AS "Minimal Quantity",
p.unity AS "Unity",
round(p.price/p.unit_price_ratio,4) AS "Unit Price",
la.description_short AS "Short description",
la.description AS "Description",
la.meta_title AS "Meta Title",
la.meta_keywords AS "Meta keywords",
la.meta_description AS "Meta description",
la.link_rewrite AS "URL rewritten",
la.available_now AS "Text when in stock",
GROUP_CONCAT(DISTINCT
    CONCAT('http://',su.domain,
        ifnull(su.physical_uri,'/'),
        'img/p/',mid(i.id_image,1,1),'/',
        if (length(i.id_image)>1,concat(mid(i.id_image,2,1),'/'),''),
       if (length(i.id_image)>2,concat(mid(i.id_image,3,1),'/'),''),
       if (length(i.id_image)>3,concat(mid(i.id_image,4,1),'/'),''),
       if (length(i.id_image)>4,concat(mid(i.id_image,5,1),'/'),''),
       i.id_image, '.jpg') SEPARATOR '|'
   ) AS 'Images Urls(x|y|z)',
GROUP_CONCAT(DISTINCT (il.legend) SEPARATOR '|') AS "Image alt texts (x|y|z)",
GROUP_CONCAT(DISTINCT(concat(fl.name,':',fvl.value)) SEPARATOR "|") AS "Features (x|y|z)",
GROUP_CONCAT(DISTINCT (a.id_product_2) SEPARATOR '|') AS "Accessories (x|y|z)",
sa.quantity AS "stock"
FROM perline_product p
LEFT JOIN perline_product_lang la ON (p.id_product = la.id_product AND la.id_lang=1)
LEFT JOIN perline_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN perline_image i ON (p.id_product = i.id_product)
LEFT JOIN perline_shop_url su ON (p.id_shop_default = su.id_shop)
LEFT JOIN perline_image_lang il ON (i.id_image = il.id_image)
LEFT JOIN perline_feature_product fp ON (fp.id_product=p.id_product)
LEFT JOIN perline_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang=1)
LEFT JOIN perline_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang=1)
LEFT JOIN perline_product_lang pl ON (fp.id_product = pl.id_product AND la.id_lang=1)
LEFT JOIN perline_accessory a ON (a.id_product_1 = p.id_product)
LEFT JOIN perline_stock_available sa ON (sa.id_product = p.id_product)
GROUP BY p.id_product;

Merci par avance pour toute piste apportée ;)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More