Une fois que t'as relié toutes tes tables ensemble, c'est le moment d'écrire quelques requêtes. Enfin, "quelques" c'est pour les débutants. Toi t'es déjà un pro, donc tu vas devoir écrire 50(!) requêtes pour ta base de données. Et c'est que les plus utiles.
Requêtes à la base de données
1. Récupérer la liste des produits pour la vitrine
La requête renvoie tous les produits actifs avec leur prix principal et image pour l'affichage sur la page d'accueil et dans le catalogue. Ça permet de générer la vitrine rapidement et de garder les infos produits à jour.
2. Recherche de produits par mot-clé
Permet aux utilisateurs de trouver les produits qui les intéressent par correspondance dans le nom ou la description. C'est une partie essentielle du front pour une recherche rapide dans le catalogue.
3. Fiche produit par ID
Renvoie les infos détaillées d'un produit, y compris la marque et la catégorie. Nécessaire pour afficher la page produit en détail.
4. Liste des variantes du produit
Affiche toutes les variantes (SKU) d'un produit : tailles, couleurs, stocks, prix. Utilisé pour choisir la bonne modif sur la page produit.
5. Galerie d'images du produit
Pour afficher la fiche produit à fond, il faut toutes ses photos. La requête les renvoie avec l'indication de l'image principale.
6. Note moyenne et nombre d'avis sur le produit
Utilisé pour afficher la note du produit et le nombre d'avis, super important pour la réputation et la confiance des clients.
7. Liste détaillée des avis sur le produit
Pour la section avis dans la fiche produit : note, texte, auteur et date de l'avis. Ça aide les nouveaux clients à décider d'acheter.
8. Questions et réponses sur le produit
Requête pour récupérer les questions/réponses pour chaque produit, utile pour le bloc FAQ sur la fiche produit.
9. Catégories de produits avec hiérarchie
Permet de visualiser la structure du catalogue, construire l'arbre de navigation pour les filtres et menus.
10. Produits par catégorie et sous-catégories
Aide à afficher tous les produits d'une catégorie choisie ou de ses "filles" (niveau d'imbrication).
11. Liste des marques
Pour filtrer par marques, créer des listings de marques et des landing pages.
12. Tags populaires et nombre de produits par tag
Analyse les tags les plus utilisés pour afficher les produits tendance et construire un nuage de tags.
13. Historique des changements de prix d'un produit
Pour l'analytics et afficher la dynamique des prix (ancien/nouveau prix, promos).
14. Historique des changements de statut du produit
Permet de suivre le cycle de vie du produit, la raison de sa disparition de la vitrine ou d'un retour.
15. Recherche par certificats et licences
Critique pour les acheteurs pros et le B2B (qualité et légalité du produit).
16. Infos sur les fournisseurs du produit
Important pour l'admin, le contrôle qualité et le contact avec les fournisseurs.
17. Stocks de produits par entrepôt
Contrôle et suivi des stocks actuels par entrepôt. Nécessaire pour la logistique et éviter le "out of stock".
18. Produits avec stock sous le seuil
Automatisation du réassort, éviter de perdre des ventes à cause de ruptures.
19. Mouvement de produits en entrepôt (audit)
Suivi de tous les mouvements de produits sur une période : entrées, sorties, corrections, utile pour l'inventaire et éviter les pertes.
20. Logistique des transferts entre entrepôts
Permet de voir l'historique et le statut des transferts internes entre centres logistiques.
21. Livraison : modes et tarifs
Pour calculer le coût de livraison et informer l'utilisateur lors de la commande.
22. Historique des commandes utilisateur
La partie la plus importante du compte perso — toutes les commandes passées, leur statut et le montant.
23. Détails de la commande avec les positions
Permet d'obtenir toute la structure de la commande — contenu, prix, quantité — pour l'affichage front ou le support.
24. Rapport sur les commandes par période et statut
Analytics et reporting sur les ventes, renvoie les commandes par période et statut voulu (genre "terminée").
25. "Paniers abandonnés"
Analytics pour les marketeux : paniers où l'utilisateur n'a pas commandé — potentiel pour du retargeting.
26. Top ventes
Analytics pour le bloc "Meilleures ventes" et les sélections marketing : quels produits sont le plus achetés.
27. Ventes par jour (pour les graphiques)
Rapport sur le CA journalier — base pour analyser la dynamique du business et faire des graphes.
28. Liste des retours
Affiche les retours sur toutes les commandes avec la raison et le statut, aide à analyser les causes de retour.
29. Liste des annulations de commandes
Contrôle des pertes et raisons d'annulation : affiche les annulations avec la raison, qui a annulé et quand.
30. Commandes en attente d'expédition
Pour l'entrepôt et la livraison — commandes à préparer et expédier, avec les détails de livraison.
31. Panier moyen
La métrique "Average Order Value" — clé pour évaluer l'efficacité du marketing et de l'assortiment.
32. Commandes avec utilisation de promo codes
Analytics sur l'efficacité des promos : quels codes ont été utilisés et à quelle fréquence.
33. Utilisation des remises par catégories et marques
Permet de voir quelles promos marchent et de suivre la popularité des remises par catégorie et marque.
34. Promo codes utilisés et leurs utilisateurs
Contrôle de l'utilisation des codes, détection d'anomalies et d'abus.
35. Historique des paiements par commande
Pour le support et la compta : affiche toutes les transactions de paiement, leurs statuts et les méthodes utilisées.
36. Commandes avec remboursement
Pour analyser les retours, générer des rapports comptables et éviter la fraude.
37. Solde du wallet utilisateur et historique des transactions
Contrôle et affichage des bonus ou cashback de l'utilisateur, historique de leurs mouvements.
38. Tickets de support utilisateur
Permet à l'utilisateur de voir ses demandes et leur statut de traitement.
39. Analytics SLA sur les tickets de support
Analyse le temps moyen de réponse et de résolution par priorité, important pour le contrôle du SLA.
40. Messages du ticket de support
Permet de voir toute la conversation sur un ticket, utile pour l'utilisateur et le support.
41. FAQ actives par catégorie
Affiche les questions fréquentes pour la base de connaissances client, aide à réduire la charge du support.
42. Campagnes marketing actives et bannières
Pour afficher les offres pub en cours sur le site.
43. Produits mis en avant sur la page d'accueil
Pour le bloc "Favoris" : produits à mettre en avant sur la home.
44. Historique des A/B tests
Analyse des expériences menées pour optimiser l'UX et le marketing.
45. Historique des vues produit par utilisateur
Affiche "Vous avez regardé" ou sert pour les recommandations personnalisées.
46. Requêtes de recherche populaires des utilisateurs
Analyse de la demande, aide à optimiser la recherche et les suggestions.
47. Analytics sur les sources de trafic
Permet de voir quels canaux pub ramènent du trafic et des conversions.
48. Rétention des utilisateurs par cohortes
Métrique clé pour évaluer la fidélité et les achats récurrents.
49. News/articles pour la home
Pour afficher les news et articles de blog, booster l'engagement des utilisateurs.
50. Pages actives du site et blocs de contenu liés
Pour vérifier la cohérence du contenu du site, le fonctionnement du CMS et l'affichage des données sur les pages.
On ajoute des index
Les requêtes c'est cool, mais seulement si ça tourne vite. Donc tu vas devoir ajouter quelques index à ta base. Faut ajouter 40 index sur les tables principales du projet pour booster les perfs et faciliter l'exploitation.
1. Index sur product.product(status)
Presque toutes les requêtes produits filtrent par statut (genre produits actifs pour la vitrine, la recherche, etc.). L'index accélère la sélection des produits d'un certain statut, en évitant de scanner toute la table.
2. Index sur product.variant(product_id, is_active)
Les requêtes sur les variantes (SKU) et la vitrine filtrent par produit et par activité. Cet index composite permet de choper toutes les variantes actives d'un produit rapidement.
3. Index sur product.image(product_id, is_main DESC)
Pour récupérer l'image principale d'un produit (ou toute la liste), on filtre par produit et on trie par "principale". L'index accélère ces sélections et permet de servir les galeries vite fait.
4. Index sur product.product(name text_pattern_ops)
Pour la recherche rapide par mot-clé dans le nom via ILIKE '%...%'. Un index spécialisé sur name text_pattern_ops booste la recherche par sous-chaîne, surtout sur de gros volumes.
5. Index sur product.product(description gin_trgm_ops)
Pareil que le 4 — recherche dans la description (ILIKE ou full-text). Un index GIN avec trigrammes accélère le filtrage sur les champs texte.
6. Index sur product.product(category_id)
Souvent on sélectionne par catégorie ou sous-catégories (voir les requêtes de filtre catalogue). L'index permet de trouver vite tous les produits d'une catégorie donnée.
7. Index sur product.category(parent_id)
Pour construire la hiérarchie des catégories et l'arbre de navigation, on sélectionne souvent par parent_id. L'index accélère ces requêtes récursives.
8. Index sur product.review(product_id)
Toutes les requêtes sur les avis filtrent par product_id (pour la note moyenne ou la liste des avis). L'index sur ce champ rend l'agrégation et la sélection des avis bien plus rapide.
9. Index sur product.review(product_id, created_at DESC)
Pour choper rapidement les derniers avis (ORDER BY createdat DESC), surtout avec un filtre sur productid, l'index composite aide beaucoup.
10. Index sur product.question(product_id, created_at DESC)
Requête fréquente sur les réponses d'un produit, triées par date de création. L'index couvre les deux conditions et accélère la section Q&A sur la fiche produit.
11. Index sur product.answer(question_id, created_at)
Pour chercher les réponses aux questions produit, accès rapide par question_id, souvent trié par date. Cet index minimise la latence pour générer le Q&A.
12. Index sur product.price_history(variant_id, changed_at DESC)
L'historique des prix s'extrait vite par variante et par changements récents. Cet index accélère les requêtes analytics sur la dynamique des prix et "ancien/nouveau prix".
13. Index sur product.status_history(product_id, changed_at DESC)
Récupérer l'historique des statuts d'un produit trié par date est demandé pour l'audit et le suivi du cycle de vie. L'index composite accélère ces requêtes.
14. Index sur product.certificate(product_id)
Recherche des certificats d'un produit par son id – classique pour le B2B et les vitrines certifiées. L'index accélère ces checks.
15. Index sur product.license(product_id)
Pour chercher les licences par produit, surtout dans les requêtes filtrées par type de licence.
16. Index sur product.product_tag(tag_id)
Requête fréquente — choper tous les produits d'un tag (et l'inverse). L'index permet de croiser produits et tags vite fait pour le nuage ou les filtres.
17. Index sur product.product_tag(product_id)
Permet de savoir rapidement quels tags sont liés à un produit, accélère la sélection par tags.
18. Index sur logistics.inventory(product_id, warehouse_id)
Pour accéder instantanément au stock d'un produit dans un entrepôt (ou pour calculer sur tous les entrepôts) — critique pour la logistique, le contrôle du stock et la vitrine en temps réel.
19. Index sur logistics.inventory(variant_id)
Pour le suivi des stocks par variante (couleur/taille) et pour les rapports transversaux.
20. Index sur logistics.stock_level(product_id, warehouse_id)
Vérif rapide du seuil mini pour un produit en stock (genre pour la commande auto ou l'alerte de stock bas). Cet index sert à comparer avec inventory.
21. Index sur logistics.inventory_movement(product_id, changed_at DESC)
Permet de récupérer vite l'historique des mouvements de produit (audit) sur les dernières périodes — utile pour éviter les erreurs, analyser les pertes et contrôler les livraisons.
22. Index sur logistics.transfer(product_id, requested_at DESC)
Pour l'analyse de la logistique des transferts entre entrepôts, filtrage par produit et tri par date de demande.
23. Index sur logistics.shipping_rate(shipping_method_id, destination_zone)
Pour calculer le coût de livraison, on choisit souvent le tarif par id de méthode et zone de destination. L'index accélère les calculs pour le client lors de la commande.
24. Index sur "order".order(user_id, placed_at DESC)
Toutes les requêtes sur l'historique des commandes utilisateur filtrent par user_id et trient par date. L'index composite assure une réponse rapide pour le compte perso.
25. Index sur "order".order(status, placed_at)
Pour l'analytics et les rapports sur les commandes par période, et la recherche par statut (genre "en cours"/"terminée").
26. Index sur "order".order_item(order_id)
Extraire toutes les positions d'une commande par id — une des opérations les plus fréquentes pour détailler les commandes.
27. Index sur "order".order_item(product_id)
L'analytics des ventes et stats produits demandent des sélections rapides des positions de commande par id produit.
28. Index sur "order".return(order_id)
Lien entre retours et commandes utilisé pour le support et l'analytics des retours. L'index accélère la recherche des retours par numéro de commande.
29. Index sur "order".cancellation(order_id)
Pareil que les retours — accélère la détection des annulations pour l'analytics et le support.
30. Index sur "order".cart(user_id, updated_at DESC)
Pour trouver les derniers paniers d'un utilisateur (genre les "abandonnés"), c'est pratique d'avoir un index sur user_id trié par date de maj.
31. Index sur payment.payment_transaction(order_id)
La plupart des requêtes sur l'historique des paiements filtrent par commande. L'index donne un accès instantané aux transactions de la commande.
32. Index sur payment.refund(transaction_id)
Permet de trouver efficacement les remboursements d'une transaction pour le support, les rapports et la lutte contre la fraude.
33. Index sur payment.wallet(user_id)
Accès rapide au wallet utilisateur pour vérifier le solde et l'historique des opérations.
34. Index sur payment.wallet_transaction(wallet_id, created_at DESC)
Sélection des transactions du wallet utilisateur triées par date (genre affichage de l'historique).
35. Index sur support.support_ticket(user_id, created_at DESC)
Historique des tickets d'un utilisateur (compte perso/service client). L'index composite optimise ces sélections.
36. Index sur support.ticket_message(ticket_id, sent_at)
Pour afficher toute la conversation d'un ticket, index sur ticket et date — accélère le tri des messages par date.
37. Index sur support.ticket_sla_tracking(ticket_id)
Pour l'analytics SLA et le suivi par ticket, accès rapide aux données SLA grâce à l'index sur ticket_id.
38. Index sur marketing.promo_usage(user_id, used_at DESC)
Pour analyser l'activité des users sur les promo codes (analytics et lutte contre les abus), il faut une recherche rapide par user_id triée par date.
39. Index sur analytics.product_view(user_id, viewed_at DESC)
Stocker et analyser l'historique des vues produit par user (personnalisation, reco) demande un accès rapide par user_id trié par date de vue.
40. Index sur analytics.search_query_log(query_text)
Les requêtes populaires et leur fréquence — outil clé pour l'analytics de recherche. L'index accélère les agrégations et les stats sur le texte de la requête.
Remarque
Pour les recherches texte avec ILIKE, il est conseillé d'utiliser des index GIN avec l'extension pg_trgm, super efficaces pour la recherche par sous-chaîne et le fuzzy search. Pour les grosses tables avec agrégations ou tri par date, un index DESC sur la date accélère la sélection des derniers enregistrements.
Ça vaut le coup d'ajuster les index selon les vrais plans d'exécution et la charge, mais ceux listés ci-dessus couvrent les scénarios principaux de prod pour notre marketplace.
On ajoute des fonctions
T'es pas encore crevé ? Alors on va écrire encore quelques fonctions pour simplifier l'écriture de nos requêtes actuelles et futures. Histoire d'accélérer la réalisation des requêtes clés, réduire la duplication de code dans l'app et centraliser la logique métier côté base de données.
1. Recherche de produits par mot-clé avec tags et marques
Pourquoi faire :
La recherche classique par nom et description est limitée. Souvent, il faut aussi chercher par tags et marques. Une fonction universelle centralise la logique de recherche avancée, réduit la duplication de code et simplifie l'intégration front.
2. Récupérer la fiche produit complète par ID (toutes les données pour la fiche)
Pourquoi faire :
Sur le front, on a souvent besoin de toutes les infos d'un produit d'un coup : champs principaux, marque, catégorie, images, tags, attributs, note moyenne et nombre d'avis. La fonction génère la fiche complète en un appel, réduisant le nombre de requêtes à la DB.
3. Récupérer la hiérarchie des catégories imbriquées
Pourquoi faire :
Construire l'arbre (ou le chemin) des catégories est nécessaire pour la vitrine, les filtres et les breadcrumbs. Au lieu de requêtes récursives côté client, la fonction renvoie toute la hiérarchie d'un coup.
4. Calcul du prix moyen et min/max par catégorie
Pourquoi faire :
Pour les filtres du catalogue et l'analytics, c'est pratique d'avoir des stats agrégées sur les produits d'une catégorie : plage de prix, moyenne. La fonction évite les sous-requêtes répétées.
5. Vérification et calcul auto du stock produit sur tous les entrepôts
Pourquoi faire :
Permet de connaître instantanément le stock total d'un produit (et de chaque variante), utile pour la vitrine, l'entrepôt et la logistique. Centralise le calcul, évite la duplication de logique métier.
6. Récupérer l'historique des commandes utilisateur avec détails
Pourquoi faire :
La fonction renvoie la liste des commandes d'un user, y compris les positions, montants, statuts, permettant au front de construire le compte perso en un appel.
7. Récupérer la note moyenne d'un utilisateur comme vendeur/acheteur
Pourquoi faire :
Pour afficher la confiance et la réputation d'un user sur la plateforme, il faut connaître sa note moyenne comme vendeur ou acheteur. La fonction fait le calcul agrégé.
8. Utilisation d'un promo code par l'utilisateur (validator avec toutes les conditions)
Pourquoi faire :
Toute la logique de vérif et d'utilisation du code (actif, limites, date, etc.) est centralisée dans une fonction. Ça simplifie la logique de l'app et protège contre les bugs de duplication de conditions.
9. Fonction universelle de log des événements utilisateur
Pourquoi faire :
Pour l'analytics transverse et l'audit, un log centralisé des events réduit la duplication de code et le risque de perdre des infos sur les actions utilisateur.
10. Fonction pour récupérer le solde du wallet bonus et le total des crédits
Pourquoi faire :
Un seul appel permet d'avoir le solde actuel de l'utilisateur et le total des crédits sur le wallet. Pratique pour l'affichage sur le dashboard et réduit le nombre de requêtes SQL.
11. Fonction universelle de changement de statut de commande avec log
Pourquoi faire :
Change le statut de la commande, ajoute une entrée dans le log d'historique des statuts et minimise les erreurs lors des changements dans différentes parties de l'app.
12. Récupérer tous les messages d'un ticket support (ticket + tous les messages)
Pourquoi faire :
La fonction renvoie toute la conversation d'un ticket, y compris les détails de la demande et chaque message. Ça facilite la construction de l'historique côté front.
13. Vérification de l'existence d'un utilisateur par email ou téléphone
Pourquoi faire :
Utilisé pour l'inscription et la récup de mot de passe, évite la duplication de logique côté front et back.
Remarque
Ce set de fonctions couvre les scénarios business clés, rend la gestion des données plus pratique, optimise la logique et accélère le dev du front et des intégrations. J'espère que t'as kiffé :)
Fichiers avec la solution
GO TO FULL VERSION