Dopo che hai collegato tutte le tabelle del tuo database, è il momento di scrivere qualche query. Anche se "qualche" è roba da principianti. Ormai sei un pro, quindi dovrai scrivere ben 50(!) query per il tuo database. E queste sono solo le più importanti.
Query al database
1. Ottenere la lista dei prodotti per la vetrina
La query restituisce tutti i prodotti attivi con il loro prezzo principale e immagine per mostrarli sulla home e nel catalogo. Così puoi costruire la vetrina al volo e mantenere le info sui prodotti sempre aggiornate.
2. Ricerca prodotti per parola chiave
Permette agli utenti di trovare i prodotti che gli interessano cercando nel nome o nella descrizione. È una parte fondamentale per la ricerca veloce nel catalogo.
3. Scheda prodotto per ID
Restituisce info dettagliate su un prodotto specifico, incluso brand e categoria. Serve per mostrare la pagina prodotto nei dettagli.
4. Lista delle varianti del prodotto
Mostra tutte le varianti disponibili (SKU) di un prodotto: taglie, colori, stock, prezzi. Serve per scegliere la modifica giusta nella pagina prodotto.
5. Galleria immagini del prodotto
Per una scheda prodotto completa servono tutte le sue foto. La query restituisce tutte le immagini indicando quella principale.
6. Valutazione media e numero di recensioni del prodotto
Serve per mostrare il rating del prodotto e il numero di recensioni, importante per la reputazione e la fiducia degli acquirenti.
7. Lista dettagliata delle recensioni del prodotto
Per la sezione recensioni nella scheda prodotto: rating, testo, autore e data della recensione. Aiuta i nuovi clienti a decidere se comprare.
8. Domande e risposte sul prodotto
Query per ottenere domande e risposte su ogni prodotto, utile per il blocco Frequently Asked Questions nella scheda prodotto.
9. Categorie dei prodotti con gerarchia
Permette di visualizzare la struttura del catalogo, costruire l’albero di navigazione per filtri e menu.
10. Prodotti per categoria e sottocategorie
Aiuta a mostrare tutti i prodotti di una categoria scelta o delle sue "figlie" (livello di annidamento).
11. Lista dei brand
Per filtrare per brand, creare listing di brand e landing page.
12. Tag popolari e numero di prodotti per tag
Analizza i tag più usati per mostrare i prodotti di tendenza e costruire la nuvola dei tag.
13. Storico dei cambi di prezzo per prodotto
Per l’analisi e per mostrare l’andamento dei prezzi (vecchio/nuovo prezzo, promozioni).
14. Storico dei cambi di stato del prodotto
Permette di tracciare il ciclo di vita del prodotto, il motivo della sua sparizione dalla vetrina o del ritorno.
15. Ricerca per certificati e licenze
Fondamentale per clienti professionali e B2B (qualità e legalità dei prodotti).
16. Dati sui fornitori del prodotto
Importante per amministrazione, controllo qualità e contatto con i fornitori.
17. Stock prodotto per magazzino
Controllo e gestione degli stock attuali per magazzino. Necessario per la logistica e per evitare "out of stock".
18. Prodotti con stock sotto soglia
Automatizza il riassortimento, previene la perdita di vendite per mancanza prodotto.
19. Movimentazione prodotto in magazzino (audit)
Traccia tutti i movimenti del prodotto in un periodo: arrivi, scarichi e correzioni, importante per inventario e prevenzione perdite.
20. Logistica dei trasferimenti tra magazzini
Permette di vedere la storia e lo stato dei movimenti interni tra centri logistici.
21. Spedizione: metodi e tariffe
Per calcolare il costo di spedizione e informare l’utente durante l’ordine.
22. Storico ordini dell’utente
La parte più importante dell’area personale — tutti gli ordini fatti, il loro stato e l’importo.
23. Dettagli ordine con le posizioni
Permette di ottenere la struttura completa dell’ordine — cosa c’è dentro, prezzi, quantità — per mostrarlo sul front o per il supporto.
24. Report ordini per periodo e stato
Analisi e report sulle vendite, restituisce gli ordini per periodo e stato richiesto (tipo "completato").
25. "Carrelli abbandonati"
Analisi per i marketer: carrelli per cui l’utente non ha completato l’ordine — potenziale per retargeting.
26. Top vendite
Analisi per il blocco "Best seller" e raccolte marketing: quali prodotti vengono comprati di più.
27. Vendite per giorno (per grafici)
Report sugli incassi giornalieri — base per analizzare l’andamento del business e costruire grafici.
28. Lista dei resi
Mostra i resi per tutti gli ordini con motivo e stato, utile per analizzare le cause dei resi.
29. Lista delle cancellazioni ordini
Controllo delle perdite e motivi di cancellazione: mostra le cancellazioni con motivo, chi ha cancellato e quando.
30. Ordini in attesa di spedizione
Per magazzino e spedizioni — ordini da preparare e spedire, con dettagli sulla spedizione.
31. Scontrino medio
La metrica "Average Order Value" — chiave per valutare l’efficacia di marketing e assortimento.
32. Ordini con uso di promocode
Analisi dell’efficacia delle promo: quali promocode sono stati usati e quanto spesso.
33. Uso degli sconti per categoria e brand
Permette di valutare quali promo funzionano e monitorare la popolarità degli sconti per categoria e brand.
34. Promocode usati e i loro utenti
Controllo sull’uso dei promocode, individuazione di anomalie e abusi.
35. Storico pagamenti per ordine
Per supporto e contabilità: mostra tutte le transazioni di pagamento, i loro stati e i metodi usati.
36. Ordini con rimborso
Per analisi dei resi, generazione di report contabili e prevenzione frodi.
37. Saldo wallet utente e storico transazioni
Controllo e visualizzazione dei bonus o cashback dell’utente, storico dei movimenti.
38. Ticket utente al supporto
Permette all’utente di vedere le sue richieste e lo stato di lavorazione.
39. SLA-analisi sui ticket di supporto
Analizza il tempo medio di risposta e risoluzione per ogni priorità, importante per il controllo SLA.
40. Messaggi del ticket di supporto
Permette di vedere tutta la conversazione sul ticket scelto, utile per utente e supporto.
41. FAQ attive per categoria
Mostra le domande frequenti per la knowledge base del cliente, aiuta a ridurre il carico sul supporto.
42. Campagne marketing attive e banner
Per mostrare le offerte pubblicitarie attuali sul sito.
43. Prodotti in evidenza sulla home
Per il blocco "Preferiti": prodotti da mettere in risalto sulla home.
44. Storico A/B test
Analisi degli esperimenti fatti per ottimizzare UX e marketing.
45. Storico visualizzazioni prodotto da parte di un utente
Mostra "Hai visto" o serve per raccomandazioni personalizzate.
46. Query di ricerca popolari degli utenti
Analisi della domanda degli utenti, aiuta a ottimizzare ricerca e suggerimenti.
47. Analisi delle fonti di traffico
Permette di valutare quali canali pubblicitari portano traffico e conversioni.
48. Retention utenti per coorti
Metrica chiave per valutare la fedeltà e gli acquisti ripetuti.
49. News/articoli per la home
Per mostrare news e articoli nei blog, aumentare l’engagement degli utenti.
50. Pagine attive del sito e blocchi di contenuto collegati
Per controllare l’integrità dei contenuti del sito, funzionamento del CMS e visualizzazione dati sulle pagine.
Aggiungiamo gli indici
Le query sono belle, ma solo se vanno veloci. Quindi dovrai aggiungere qualche indice al tuo database. Dovresti aggiungere 40 indici alle tabelle principali del progetto per migliorare le performance delle query e la gestione.
1. Indice su product.product(status)
Quasi tutte le query sui prodotti filtrano per status (tipo prodotti attivi per vetrina, ricerca ecc). L’indice velocizza la selezione dei prodotti con uno status specifico, minimizzando la scansione della tabella.
2. Indice su product.variant(product_id, is_active)
Le query sulle varianti prodotto (SKU) e per la vetrina usano il filtro per collegamento al prodotto e per attività della variante. Questo indice composto permette di selezionare tutte le varianti attive di un prodotto in modo ottimale.
3. Indice su product.image(product_id, is_main DESC)
Per ottenere l’immagine principale del prodotto (o tutta la lista) si filtra per prodotto e si ordina per “principale”. L’indice velocizza queste selezioni e garantisce dati rapidi per le gallerie.
4. Indice su product.product(name text_pattern_ops)
Per la ricerca veloce dei prodotti per parola chiave nel nome tramite ILIKE '%...%'. Un indice specializzato su name text_pattern_ops migliora la ricerca per sottostringa, soprattutto su grandi volumi.
5. Indice su product.product(description gin_trgm_ops)
Come sopra — ricerca nella descrizione prodotto (ILIKE o full-text). Un indice GIN con trigrammi velocizza il filtro sui campi testo.
6. Indice su product.product(category_id)
Spesso si seleziona per categoria o per categorie figlie (vedi query di filtro per categorie). L’indice permette di trovare velocemente tutti i prodotti di una categoria.
7. Indice su product.category(parent_id)
Per costruire la gerarchia delle categorie e visualizzare l’albero di navigazione si seleziona spesso per parent_id. L’indice velocizza queste query ricorsive.
8. Indice su product.review(product_id)
Tutte le query sulle recensioni filtrano per product_id (sia per rating medio che per lista recensioni). L’indice su questo campo rende aggregazione e selezione molto più veloci.
9. Indice su product.review(product_id, created_at DESC)
Per ottenere velocemente le ultime recensioni (ORDER BY createdat DESC), soprattutto insieme al filtro per productid, serve un indice composto.
10. Indice su product.question(product_id, created_at DESC)
Query popolare per risposte su un prodotto, con ordinamento per data creazione. L’indice copre entrambe le condizioni e velocizza la sezione Q&A nella scheda prodotto.
11. Indice su product.answer(question_id, created_at)
Per trovare le risposte alle domande serve accesso rapido per chiave esterna question_id, spesso con ordinamento per data. Questo indice minimizza i tempi per generare Q&A.
12. Indice su product.price_history(variant_id, changed_at DESC)
Lo storico dei prezzi si estrae velocemente per variante e per cambi recenti. Questo indice velocizza le query analitiche su prezzi vecchi/nuovi.
13. Indice su product.status_history(product_id, changed_at DESC)
Selezionare lo storico degli status per prodotto con ordinamento per data è richiesto per audit e controllo ciclo di vita. L’indice composto velocizza molto queste query.
14. Indice su product.certificate(product_id)
Ricerca dei certificati prodotto per id — tipica per B2B e vetrine certificate. L’indice velocizza questi controlli.
15. Indice su product.license(product_id)
Per cercare licenze sui prodotti, specie con filtro per tipo licenza.
16. Indice su product.product_tag(tag_id)
Query frequente — ottenere tutti i prodotti per un certo tag (e viceversa). L’indice permette di incrociare prodotti e tag velocemente per nuvola tag o filtri.
17. Indice su product.product_tag(product_id)
Permette di vedere subito quali tag sono collegati a un prodotto, velocizzando la selezione per tag.
18. Indice su logistics.inventory(product_id, warehouse_id)
Per accesso istantaneo allo stock prodotto in magazzino (o per calcolo su tutti i magazzini) — critico per logistica, controllo stock level e vetrine in tempo reale.
19. Indice su logistics.inventory(variant_id)
Per la gestione degli stock per variante (colore/taglia) e per report trasversali.
20. Indice su logistics.stock_level(product_id, warehouse_id)
Controllo veloce della soglia minima per prodotto in magazzino (tipo per auto-ordine o allarme stock basso). Serve per confronto con inventory.
21. Indice su logistics.inventory_movement(product_id, changed_at DESC)
Permette di ottenere velocemente lo storico dei movimenti prodotto (audit) negli ultimi periodi — utile per prevenire errori, analizzare perdite e controllare forniture.
22. Indice su logistics.transfer(product_id, requested_at DESC)
Per analizzare la logistica dei trasferimenti tra magazzini, filtro per prodotto e ordinamento per data richiesta.
23. Indice su logistics.shipping_rate(shipping_method_id, destination_zone)
Nel calcolo del costo spedizione si seleziona spesso la tariffa per id metodo e zona destinazione. L’indice velocizza i calcoli per il cliente durante l’ordine.
24. Indice su "order".order(user_id, placed_at DESC)
Tutte le query sullo storico ordini dell’utente filtrano per user_id e ordinano per data ordine. L’indice composto garantisce risposta rapida per l’area personale.
25. Indice su "order".order(status, placed_at)
Per analisi e report sugli ordini per periodo, e ricerca per status (tipo "in lavorazione"/"completato").
26. Indice su "order".order_item(order_id)
Estrarre tutte le posizioni ordine per id ordine — una delle operazioni più frequenti per dettaglio ordini.
27. Indice su "order".order_item(product_id)
Analisi vendite e statistiche per prodotto richiedono selezioni rapide delle posizioni ordine per id prodotto.
28. Indice su "order".return(order_id)
Collegamento dei resi agli ordini usato per supporto e analisi resi. L’indice velocizza la ricerca dei resi per numero ordine.
29. Indice su "order".cancellation(order_id)
Come per i resi — velocizza l’individuazione delle cancellazioni per analisi e supporto.
30. Indice su "order".cart(user_id, updated_at DESC)
Per trovare gli ultimi carrelli dell’utente (tipo ricerca "carrelli abbandonati"), è comodo avere l’indice per user_id e data ultimo aggiornamento.
31. Indice su payment.payment_transaction(order_id)
La maggior parte delle query sullo storico pagamenti filtra per ordine. L’indice garantisce accesso istantaneo alle transazioni dell’ordine.
32. Indice su payment.refund(transaction_id)
Permette di trovare velocemente i rimborsi per una transazione, utile per supporto, report e controllo frodi.
33. Indice su payment.wallet(user_id)
Accesso rapido al wallet utente per controllare saldo e storico operazioni.
34. Indice su payment.wallet_transaction(wallet_id, created_at DESC)
Selezione delle transazioni wallet utente con ordinamento per data (tipo per mostrare lo storico operazioni).
35. Indice su support.support_ticket(user_id, created_at DESC)
Storico delle richieste utente al supporto (area personale/servizio clienti). L’indice composto ottimizza queste selezioni.
36. Indice su support.ticket_message(ticket_id, sent_at)
Per mostrare tutta la conversazione del ticket è comodo avere l’indice per ticket e data — velocizza l’ordinamento dei messaggi.
37. Indice su support.ticket_sla_tracking(ticket_id)
Per SLA-analisi e controllo per ogni ticket, accesso rapido ai dati SLA grazie all’indice per ticket_id.
38. Indice su marketing.promo_usage(user_id, used_at DESC)
Per analisi attività utenti sui promocode (sia analisi che prevenzione abusi), serve ricerca veloce per user_id e ordinamento per data.
39. Indice su analytics.product_view(user_id, viewed_at DESC)
Memorizzare e analizzare lo storico delle visualizzazioni prodotto dell’utente (personalizzazione, raccomandazioni) richiede accesso rapido per user_id e ordinamento per data.
40. Indice su analytics.search_query_log(query_text)
Query popolari e frequenza d’uso — strumento chiave per l’analisi della ricerca. L’indice velocizza aggregazioni e conteggi per testo query.
Nota
Per le ricerche testuali con ILIKE si consiglia di usare indici GIN con estensione pg_trgm, molto efficaci per ricerca sottostringa e fuzzy search. Per tabelle grandi con aggregazione o ordinamento per data, conviene un indice DESC sulla data — velocizza la selezione degli ultimi record.
Ha senso configurare gli indici secondo i reali execution plan e statistiche di carico, ma quelli sopra coprono gli scenari principali di produzione per il nostro marketplace.
Aggiungiamo le funzioni
Non sei ancora stanco? Allora scriviamo ancora qualche funzione per semplificare la scrittura delle nostre query attuali e future. Così acceleriamo l’implementazione delle query chiave, riduciamo la duplicazione del codice nell’app e centralizziamo la business logic lato database.
1. Ricerca prodotti per parola chiave considerando tag e brand
Perché serve:
La ricerca normale su nome e descrizione è limitata. Spesso serve cercare anche per tag e brand. Una funzione universale centralizza la logica della ricerca avanzata, riduce la duplicazione del codice e semplifica l’integrazione col frontend.
2. Ottenere la scheda completa del prodotto per ID (tutti i dati per la scheda)
Perché serve:
Sul frontend spesso serve subito tutta l’info sul prodotto: campi principali, brand, categoria, immagini, tag, attributi, rating medio e numero recensioni. La funzione costruisce la scheda completa con una sola chiamata, riducendo le query al DB.
3. Ottenere la gerarchia delle categorie con annidamento
Perché serve:
Costruire l’albero (o il percorso) delle categorie serve per vetrina, filtri e breadcrumbs. Invece di query ricorsive lato client, la funzione restituisce tutta la gerarchia in una volta.
4. Calcolo del prezzo medio e valore min/max per categoria
Perché serve:
Per i filtri nel catalogo e l’analisi è comodo avere statistiche aggregate sui prodotti di una categoria: range prezzi, media. La funzione evita subquery ripetitive.
5. Verifica e calcolo automatico dello stock prodotto su tutti i magazzini
Perché serve:
Permette di sapere subito lo stock totale di un prodotto (e per ogni variante), utile per vetrina, magazzino e logistica. Centralizza il calcolo, evitando duplicazione della business logic.
6. Ottenere lo storico ordini utente con dettagli
Perché serve:
La funzione restituisce la lista degli ordini utente, incluse le posizioni, importi, status, così il frontend può costruire l’area personale con una sola chiamata.
7. Ottenere il rating medio utente come venditore/acquirente
Perché serve:
Per mostrare fiducia e reputazione utente sulla piattaforma è importante conoscere il suo rating medio come venditore o acquirente. La funzione fa il calcolo aggregato.
8. Uso del promocode da parte dell’utente (validator con tutte le condizioni)
Perché serve:
Tutta la business logic di verifica e utilizzo del promocode (attività, limiti, data ecc.) è centralizzata in una funzione. Così la logica dell’app è più semplice e si evitano errori per condizioni duplicate.
9. Funzione universale di logging degli eventi utente
Perché serve:
Per analisi e audit end-to-end, il logging centralizzato degli eventi riduce la duplicazione del codice e il rischio di perdere dati sulle azioni utente.
10. Funzione per ottenere saldo wallet bonus e totale accrediti
Perché serve:
Una sola chiamata permette di ottenere subito il saldo attuale utente e il totale degli accrediti sul wallet. Utile per la dashboard e per ridurre il numero di query SQL.
11. Funzione universale per cambio status ordine con logging
Perché serve:
Cambia lo status ordine, aggiunge una riga allo storico status e minimizza errori nel cambio status in varie parti dell’app.
12. Ottenere tutti i messaggi della conversazione di supporto (ticket + messaggi)
Perché serve:
La funzione restituisce tutta la conversazione del ticket, inclusi dettagli e ogni messaggio. Così è più facile costruire la storia del ticket sul frontend.
13. Verifica esistenza utente per email o telefono
Perché serve:
Serve per registrazione e recupero password, evita duplicazione della logica su frontend e backend.
Nota
Questa raccolta di funzioni copre gli scenari business chiave, migliora la gestione dei dati, ottimizza la logica e accelera lo sviluppo di frontend e integrazioni. Spero ti sia piaciuto :)
File con la soluzione
GO TO FULL VERSION