Quelques astuces Mysql

31 Juillet 2018 - par Olivier LE CORRE

Un poste un peu technique pour présenter quelques petites astuces Mysql ou préconisations que nous avons dans notre coin et qui peuvent servir, j'ai eu envi de les partager.
Nos astuces sont utilisés dans l'environnement PrestaShop mais peuvent être utilisées ailleurs. Se sont parfois des commandes ou fonctions très peu connu.

Rappel sur les jointures

LEFT JOIN, INNER JOIN, RIGHT JOIN, ca fonction comment ? voici un tutorial que j'ai écris y pas mal d'année maintenant pour un magazine PHP disparue "Direction PHP". Télécharger le tutoriel

J'en profites pour ajouter quelques préconisations ici de mon expérience de vieux développeurs.
Quand nous écrivons des requêtes SQL avec plusieurs tables et jointures, déjà ne pas faire de jointure dans le WHERE et voici la structure.

  • SELECT
  • FROM
  • LES JOINTURES
  • WHERE
  • HAVING
  • ORDER BY

la requête SQL est plus lisible comme ca, surtout 6 mois après l'avoir écrite :

SELECT DISTINCT p.id_product, pl.name, pa.id_product_attribute, p.weight as poids, pa.weight as poids2, p.reference, pa.reference as ref2, pa.ean13 as ean2, pl.description_short, GROUP_CONCAT(pal.name ORDER BY pal.name) as name2, p.ean13, p.price
FROM `PS_product` p
INNER JOIN `PS_product_lang` pl ON (p.id_product=pl.id_product AND pl.id_lang=1)
LEFT JOIN `PS_product_attribute` pa ON (p.id_product=pa.id_product)
LEFT JOIN `PS_product_attribute_combination` pac ON (pac.id_product_attribute=pa.id_product_attribute)
LEFT JOIN `PS_attribute_lang` pal ON (pac.id_attribute=pal.id_attribute AND pl.id_lang=1)
WHERE p.active=1 AND p.id_product>=31513664
GROUP BY p.id_product, pl.name, pa.id_product_attribute, p.weight, pa.weight, p.reference, pa.reference, pa.ean13, pl.description_short,p.ean13, p.price
ORDER BY p.id_product

Ajouter plein de produits dans une catégorie directement en base de données

Notre besoin :
Pour un site avec un grand nombre de produit, je dois ajouter à une catégory tous les produits qui ont une caractéristique précise.

La problématique :
Vous me direz ajouter des produits dans une catégorie en requête SQL facile ! oui mais les produits dans une catégorie ont une position, souvent la position est corrigé avec un script PHP qui va boucler sur tous les pdouts etc..., hors voici l'astuce pour le faire directement en requête SQL

L'astuce :

  • Ma première action, récupérer tous les produits qui ont la caractéristique 6 : SELECT id_product FROM ps_feature_product WHERE id_feature_value=6;
  • Je rajoute l'id de la catégorie 37 dans ma requête : SELECT 37, id_product FROM ps_feature_product WHERE id_feature_value=6;
  • Je rajoute une commande qui permettra d'incrémenter ma position au fur et à mesure de l'ajout
    - Je définis une variable position avec SET : SET @position := -1; -1 pour que ca démarre à 0
    - J'ajoute à la requête la commande pour incrémenter la position (@position := @position + 1)
  • Dernière action, je prépare l'ajout avec INSERT : INSERT INTO ps_category_product (id_category, id_product, position)

Au final j'obtient les deux lignes suivante a exécuter dans le phpmyadmin de ma base de données.

SET @position := -1;
INSERT INTO ps_category_product (id_category, id_product, position) SELECT 37, id_product, (@position := @position + 1) FROM ps_feature_product WHERE id_feature_value=6;

Choisir une ordre spécifique dans une requête

Notre besoin :
Affiche des caractéristiques dans un ordre précis.

La problématique :
Dans PrestaShop vous pouvez avoir les caractérisques dans un classement par position gérable dans l'administration. Sauf que dans notre cas, ce classement était utilisé pour un affichage et nous avions besoin d'un autre classement particulier de 3 caractéristiques. Ce classement ne permettait pas d'utiliser les positions, ni l'ordre croissant, l'ordre alphabétique etc...

L'astuce :
Nous avons utilisé ORDER BY FIELD, cette commande permet de définir un ordre sur un champ de la requête.
- Nous récupérons que les infos concernant notre produit et les 3 caractéristiques WHERE pf.id_product = 2047 AND pf.id_feature IN(5,6,14)
- Nous déterminons l'ordre d'affichage ORDER BY FIELD(pf.id_feature,'5','14','6')

Voici la requête finale

SELECT name, value, pf.id_feature FROM ps_feature_product pf LEFT JOIN ps_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 1) LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 1) LEFT JOIN ps_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 1) INNER JOIN ps_feature_shop feature_shop ON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1) WHERE pf.id_product = 2047 AND pf.id_feature IN(5,6,14) ORDER BY FIELD(pf.id_feature,'5','14','6')

Trouver les doublons dans une table

Un classique mais finalement pas souvent utilisé et vite oublié, je mets donc un exemple de requête

SELECT COUNT(*) AS nbr_doublon, annee, marque, modele, moteur, US FROM ps_vehicules GROUP BY annee, marque, modele, moteur, US HAVING COUNT(*) > 1

Liste les produits avec leurs options classés par ordre alphabéthique

Notre besoin :
Récupérer une liste de produit avec pour chaque produit les options classées par ordre alphabétique (déclinaisons dans PrestaShop).

L'astuce :
Utiliser un GROUP_CONCAT et lui donner un ordre de classement, si si c'est possible.

Voici la requête finale

SELECT DISTINCT p.id_product, pl.name, pa.id_product_attribute, GROUP_CONCAT(pal.name ORDER BY pal.name) as name2
FROM `PS_product` p
INNER JOIN `PS_product_lang` pl ON (p.id_product=pl.id_product AND pl.id_lang=1)
LEFT JOIN `PS_product_attribute` pa ON (p.id_product=pa.id_product)
LEFT JOIN `PS_product_attribute_combination` pac ON (pac.id_product_attribute=pa.id_product_attribute)
LEFT JOIN `PS_attribute_lang` pal ON (pac.id_attribute=pal.id_attribute AND pl.id_lang=1)
WHERE p.active=1
GROUP BY p.id_product, pl.name, pa.id_product_attribute ORDER BY p.id_product