Prestashop : 8 requêtes SQL utiles

Liste de quelques requêtes SQL utiles à connaître lors de l’utilisation ou du développement Prestashop. La Team a également mis en place un tutoriel de requêtes SQL pour les fiches produits, qui sera alimenté dans le temps afin d’aider la majorité des e-commerçants. Vous pouvez retrouver ce tutoriel en cliquant ici.

Vous êtes responsable de votre base de données. 

Requêtes SQL pour votre catalogue (import/export)

Exporter les produits et leurs attributs

SELECT 
   CONCAT(pl.name,' / ',pal.name,' / ', pq.quantity) AS required
FROM ps_product p 
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_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_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)
WHERE pl.id_lang = 1 
      AND pal.id_lang = 1 
GROUP BY p.id_product,pal.name
ORDER BY p.id_product, pac.id_attribute

Exporter les produits avec les images

SELECT p.id_product AS 'ID',
pl.id_lang AS 'ID_LANG',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
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',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition',
concat( 'http://localhost/ps16/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
WHERE 1=1
and p.active = 1

Exporter les produits avec EAN13, fabricants et référence pour Amazon

SELECT DISTINCT p.id_product, l.name,m.name, p.ean13, p.reference FROM ps_product AS p 
LEFT JOIN ps_product_lang AS l ON p.id_product = l.id_product
LEFT JOIN ps_manufacturer AS m ON p.id_manufacturer = m.id_manufacturer;

Requêtes SQL pour vos produits

Associer tous les produits dans une catégorie précise, en se basant sur un mot (présent dans le nom du produit)

INSERT INTO `PREFIX_category_product` 
SELECT 3017, `id_product`, 0 
FROM `PREFIX_product_lang` 
WHERE INSTR(name, 'MotSeTrouvantDansLeNomDesProduits') > 0
LIMIT 1;

Mettre un produit dans la catégorie la plus récente à laquelle il appartient

Le produit doit être rangé tout de même dans la catégorie. Il aura comme catégorie par défaut la plus ancienne créée et à laquelle il appartient

UPDATE agnproduct p
SET p.id_category_default = (
    SELECT MAX(cp.id_category)
    FROM agncategory_product cp
    WHERE cp.id_product = p.id_product
    AND cp.id_category = (
        SELECT c.id_category
        FROM agncategory c 
        WHERE c.id_category = cp.id_category
        AND active = 1
    )
)

UPDATE agnproduct_shop p
SET p.id_category_default = (
    SELECT MAX(cp.id_category)
    FROM agncategory_product cp
    WHERE cp.id_product = p.id_product
    AND cp.id_category = (
        SELECT c.id_category
        FROM agncategory c 
        WHERE c.id_category = cp.id_category
        AND active = 1
    )
)

Requêtes SQL pour votre référencement

Mettre la meta_description à 160 caractères en récupérant le résumé de produits

Cette fonction fait un peu écho à cet article de blog sur le référencement via Smarty

Il faut au préalable créer une fonction SQL, chose que l’on ne réalise qu’une fois

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC 
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;


UPDATE ps_product_lang 
SET meta_description = SUBSTRING(fnStripTags(description_short), 1, 255);

Autres requêtes SQL utiles

Mettre la TVA sur toutes les factures

UPDATE `ps_order_invoice` SET `total_paid_tax_incl` = (`total_paid_tax_excl` + (`total_paid_tax_excl` * 20 / 100)) WHERE `total_paid_tax_incl` = `total_paid_tax_excl`;

Lier un groupe client à toutes les catégories

Pour cela il vous faudra simplement disposer de l’identifiant du groupe client. La requête part du principe que le groupe vient d’être créé, et qu’en conséquent il n’est lié à aucune catégorie.

Dans l’exemple, pensez à remplacer le 9 par l’identifiant de votre groupe client.

        # Remplacer le 9 par l'identifiant du groupe client
        INSERT INTO trbsg_category_group (id_category, id_group)
        SELECT  DISTINCT id_category, 9
        FROM    trbsg_category;

 Imprimer