Después de que hayas conectado todas las tablas de tu base, es hora de escribir un par de consultas. Aunque, un par es para novatos. Ya eres pro, así que vas a tener que escribir 50(!) consultas para tu base de datos. Y eso solo son las más necesarias.
Consultas a la base de datos
1. Obtener lista de productos para el escaparate
La consulta devuelve todos los productos activos con su precio principal e imagen para mostrar en la página principal y en el catálogo. Así puedes montar el escaparate rápido y mantener la info de productos al día.
2. Búsqueda de productos por palabra clave
Permite a los usuarios encontrar productos que les interesan buscando coincidencias en el nombre o la descripción. Es una parte clave del frontend para buscar rápido en el catálogo.
3. Ficha de producto por ID
Devuelve info ampliada de un producto concreto, incluyendo marca y categoría. Es necesario para mostrar la página de detalle del producto.
4. Lista de variantes del producto
Muestra todas las variantes disponibles (SKU) del producto: tallas, colores, stock, precios. Se usa para elegir la modificación que quieras en la página del producto.
5. Galería de imágenes del producto
Para mostrar bien la ficha del producto, hacen falta todas sus fotos. La consulta las devuelve indicando cuál es la principal.
6. Valoración media y número de reseñas del producto
Se usa para mostrar la puntuación del producto y el número de reseñas, importante para la reputación y la confianza de los compradores.
7. Lista detallada de reseñas del producto
Para la sección de reseñas en la ficha: puntuación, texto, autor y fecha de la reseña. Ayuda a nuevos compradores a decidirse.
8. Preguntas y respuestas sobre el producto
Consulta para obtener preguntas y respuestas de cada producto, importante para el bloque de Preguntas Frecuentes en la ficha.
9. Categorías de productos con jerarquía
Permite visualizar la estructura del catálogo, construir el árbol de navegación para filtros y menús.
10. Productos por categoría y subcategorías
Ayuda a mostrar todos los productos de la categoría elegida o de sus "hijas" (nivel de anidamiento).
11. Lista de marcas
Para filtrar por marcas, crear listados de marcas y landings.
12. Tags populares y cantidad de productos por tag
Analiza los tags más usados para mostrar productos en tendencia y construir la nube de tags.
13. Historial de cambios de precio de un producto
Para analítica y mostrar la dinámica de precios (precio antiguo/nuevo, ofertas).
14. Historial de cambios de estado del producto
Permite seguir el ciclo de vida del producto, la razón de que desaparezca del escaparate o vuelva.
15. Búsqueda por certificados y licencias
Crítico para compradores profesionales y B2B (calidad y legalidad del producto).
16. Datos de proveedores del producto
Importante para administración, control de calidad y contacto con proveedores.
17. Stock de producto por almacenes
Control y gestión del stock actual por almacén. Necesario para logística y evitar "out of stock".
18. Productos con stock por debajo del umbral
Automatiza la reposición de almacén, evita perder ventas por falta de stock.
19. Movimiento de producto en almacén (auditoría)
Seguimiento de todos los movimientos de producto en un periodo: entradas, salidas y ajustes, importante para inventario y evitar pérdidas.
20. Logística de movimientos entre almacenes
Permite ver el historial y estado de movimientos internos de producto entre centros logísticos.
21. Envío: métodos y tarifas
Para calcular el coste de envío e informar al usuario al hacer el pedido.
22. Historial de pedidos del usuario
La parte más importante del área personal — todos los pedidos realizados, su estado y el importe.
23. Detalles del pedido con posiciones
Permite obtener la estructura completa del pedido — composición, precios, cantidad — para mostrar en el frontend o para soporte.
24. Informe de pedidos por periodo y estado
Analítica e informes de ventas, devuelve pedidos por periodo y estado necesario (por ejemplo, "completado").
25. "Carritos abandonados"
Analítica para marketing: carritos donde el usuario no finalizó el pedido — potencial para retargeting.
26. Top ventas
Analítica para el bloque "Más vendidos" y selecciones de marketing: qué productos se compran más.
27. Ventas por días (para gráficos)
Informe de ingresos diarios — base para analizar la dinámica del negocio y construir gráficos.
28. Lista de devoluciones
Muestra devoluciones de todos los pedidos con motivo y estado, ayuda a analizar las causas de devolución.
29. Lista de cancelaciones de pedidos
Control de pérdidas y motivos de cancelación: muestra cancelaciones con motivo, quién y cuándo canceló.
30. Pedidos pendientes de envío
Para almacén y mensajería — pedidos que hay que preparar y enviar, con detalles de envío.
31. Ticket medio
El "Average Order Value" — métrica clave para evaluar la eficacia del marketing y el surtido.
32. Pedidos con uso de códigos promocionales
Analítica de eficacia de campañas: qué códigos se usaron y con qué frecuencia.
33. Uso de descuentos por categorías y marcas
Permite ver qué campañas funcionan y monitorizar la popularidad de descuentos por categorías y marcas.
34. Códigos promocionales usados y sus usuarios
Control del uso de códigos, detección de anomalías y abusos.
35. Historial de pagos por pedido
Para soporte y contabilidad: muestra todas las transacciones de pago, sus estados y métodos usados.
36. Pedidos con devolución de dinero
Para analizar devoluciones, generar informes contables y prevenir fraudes.
37. Saldo del monedero del usuario e historial de transacciones
Control y visualización de bonos o cashback del usuario, historial de movimientos.
38. Solicitudes del usuario a soporte
Permite al usuario ver sus tickets y el estado de cada uno.
39. Analítica SLA de tickets de soporte
Analiza el tiempo medio de respuesta y resolución por prioridad, importante para controlar el SLA.
40. Mensajes del ticket de soporte
Permite ver toda la conversación del ticket, importante para usuario y soporte.
41. FAQ activos por categorías
Muestra preguntas frecuentes para la base de conocimiento del cliente, ayuda a reducir carga en soporte.
42. Campañas de marketing activas y banners
Para mostrar ofertas actuales en la web.
43. Productos destacados en la página principal
Para el bloque "Favoritos": productos que hay que destacar en la home.
44. Historial de tests A/B
Análisis de experimentos realizados para optimizar UX y marketing.
45. Historial de visualizaciones de producto por usuario
Muestra "Has visto" o se usa para recomendaciones personalizadas.
46. Consultas de búsqueda populares de los usuarios
Análisis de demanda, ayuda a optimizar la búsqueda y sugerencias.
47. Analítica de fuentes de tráfico
Permite ver qué canales de publicidad traen tráfico y conversiones.
48. Retención de usuarios por cohortes
Métrica clave para medir lealtad y compras repetidas.
49. Noticias/artículos para la home
Para mostrar noticias y artículos en blogs, aumentar la implicación de los usuarios.
50. Páginas activas del sitio y bloques de contenido relacionados
Para comprobar la integridad del contenido del sitio, funcionamiento del CMS y mostrar datos en las páginas.
Añadiendo índices
Las consultas están bien, pero solo si van rápido. Así que tendrás que añadir algunos índices a tu base. Deberías añadir 40 índices a las tablas principales del proyecto para mejorar el rendimiento y facilitar la explotación.
1. Índice en product.product(status)
Casi todas las consultas a productos filtran por estado (por ejemplo, productos activos para el escaparate, búsqueda, etc.). El índice acelera la selección de productos con un estado concreto, minimizando el escaneo de la tabla.
2. Índice en product.variant(product_id, is_active)
Las consultas a variantes de producto (SKU) y para el escaparate usan filtro por relación con producto y por actividad de la variante. Este índice compuesto permite seleccionar óptimamente todas las variantes activas de un producto concreto.
3. Índice en product.image(product_id, is_main DESC)
Para obtener la imagen principal del producto (o toda la lista) se filtra por producto y se ordena por "principal". El índice acelera estas selecciones y da datos rápidos para galerías.
4. Índice en product.product(name text_pattern_ops)
Para buscar productos rápido por palabra clave en el nombre usando ILIKE '%...%'. El índice especializado en name text_pattern_ops mejora la búsqueda por subcadena, sobre todo en grandes volúmenes.
5. Índice en product.product(description gin_trgm_ops)
Igual que el anterior — búsqueda por descripción del producto (ILIKE o full-text). El índice GIN con trigramas acelera el filtrado por campos de texto.
6. Índice en product.product(category_id)
A menudo se selecciona por categoría o por categorías hijas (ver consultas de filtro por categorías). El índice permite encontrar rápido todos los productos de una categoría dada.
7. Índice en product.category(parent_id)
Para construir la jerarquía de categorías y visualizar el árbol de navegación se consulta mucho por parent_id. El índice acelera estas consultas recursivas.
8. Índice en product.review(product_id)
Todos los accesos a reseñas de producto filtran por product_id (para media de puntuación y para lista de reseñas). El índice en este campo hace la agregación y selección mucho más rápida.
9. Índice en product.review(product_id, created_at DESC)
Para obtener rápido las últimas reseñas de un producto (ORDER BY createdat DESC), sobre todo junto con filtro por productid, ayuda el índice compuesto.
10. Índice en product.question(product_id, created_at DESC)
Consulta popular para respuestas de un producto concreto, ordenadas por fecha de creación. El índice cubre ambas condiciones y acelera la sección Q&A en la ficha.
11. Índice en product.answer(question_id, created_at)
Para buscar respuestas a preguntas de producto hace falta acceso rápido por clave externa question_id, a menudo ordenado por fecha. Este índice minimiza la latencia al generar Q&A.
12. Índice en product.price_history(variant_id, changed_at DESC)
El historial de cambios de precio se extrae rápido por variante y por cambios recientes. Este índice acelera consultas analíticas de dinámica de precios y “precio antiguo/nuevo”.
13. Índice en product.status_history(product_id, changed_at DESC)
Seleccionar el historial de cambios de estado de un producto ordenado por fecha es útil para auditoría y control del ciclo de vida. El índice compuesto acelera mucho estas consultas.
14. Índice en product.certificate(product_id)
Buscar certificados de producto por id es típico para B2B y escaparates certificados. El índice acelera estas comprobaciones.
15. Índice en product.license(product_id)
Para buscar licencias de productos, sobre todo en consultas con filtro por tipo de licencia.
16. Índice en product.product_tag(tag_id)
Consulta frecuente — obtener todos los productos por un tag (y viceversa). El índice permite cruzar productos y tags rápido para la nube de tags o filtros.
17. Índice en product.product_tag(product_id)
Permite saber rápido qué tags tiene un producto, acelerando la selección por tags.
18. Índice en logistics.inventory(product_id, warehouse_id)
Para acceso instantáneo al stock de un producto en almacén (o para calcular en todos los almacenes) — crítico para logística, comprobar stock level y escaparates en tiempo real.
19. Índice en logistics.inventory(variant_id)
Para gestionar stock por variante (color/talla) y para informes transversales.
20. Índice en logistics.stock_level(product_id, warehouse_id)
Comprobación rápida del umbral mínimo de producto en almacén (para auto-pedido o alerta de bajo stock). Este índice es necesario para comparar con inventory.
21. Índice en logistics.inventory_movement(product_id, changed_at DESC)
Permite obtener rápido el historial de movimientos de producto (auditoría) en los últimos periodos — útil para evitar errores, analizar pérdidas y controlar suministros.
22. Índice en logistics.transfer(product_id, requested_at DESC)
Para analizar la logística de movimientos entre almacenes, filtrar por producto y ordenar por fecha de solicitud.
23. Índice en logistics.shipping_rate(shipping_method_id, destination_zone)
Al calcular el coste de envío se elige tarifa por id de método y zona destino. El índice acelera los cálculos para el cliente al hacer el pedido.
24. Índice en "order".order(user_id, placed_at DESC)
Todos los accesos al historial de pedidos del usuario filtran por user_id y ordenan por fecha. El índice compuesto da acceso rápido al historial en el área personal.
25. Índice en "order".order(status, placed_at)
Para analítica e informes de pedidos por periodos, y búsqueda por estado (por ejemplo, "en proceso"/"completado").
26. Índice en "order".order_item(order_id)
Extraer todas las posiciones de un pedido por id es de las operaciones más frecuentes para detallar pedidos.
27. Índice en "order".order_item(product_id)
Analítica de ventas y estadísticas por producto requieren selecciones rápidas de posiciones de pedido por id de producto.
28. Índice en "order".return(order_id)
La relación de devoluciones con pedidos se usa para soporte y analítica de devoluciones. El índice acelera la búsqueda de devoluciones por número de pedido.
29. Índice en "order".cancellation(order_id)
Igual que devoluciones — acelera la detección de cancelaciones para analítica y soporte.
30. Índice en "order".cart(user_id, updated_at DESC)
Para buscar los últimos carritos del usuario (por ejemplo, “carritos abandonados”), es útil tener índice por user_id y fecha de última actualización.
31. Índice en payment.payment_transaction(order_id)
La mayoría de consultas de historial de pagos filtran por pedido concreto. El índice da acceso instantáneo a las transacciones del pedido.
32. Índice en payment.refund(transaction_id)
Permite encontrar devoluciones por transacción concreta para soporte, informes y control de fraude.
33. Índice en payment.wallet(user_id)
Acceso rápido al monedero del usuario para comprobar saldo e historial de operaciones.
34. Índice en payment.wallet_transaction(wallet_id, created_at DESC)
Selección de transacciones del monedero del usuario ordenadas por fecha (por ejemplo, mostrar historial de operaciones).
35. Índice en support.support_ticket(user_id, created_at DESC)
Historial de tickets de soporte de un usuario (área personal/servicio cliente). El índice compuesto optimiza estas selecciones.
36. Índice en support.ticket_message(ticket_id, sent_at)
Para mostrar toda la conversación de un ticket es útil tener índice por ticket y fecha — acelera el orden de mensajes por tiempo.
37. Índice en support.ticket_sla_tracking(ticket_id)
Para analítica SLA y control por ticket, acceso rápido a datos SLA gracias al índice por ticket_id.
38. Índice en marketing.promo_usage(user_id, used_at DESC)
Para analizar la actividad de usuarios con códigos promo (analítica y protección contra abusos), hace falta búsqueda rápida por user_id y orden por fecha.
39. Índice en analytics.product_view(user_id, viewed_at DESC)
Guardar y analizar el historial de vistas de productos por usuario (personalización, recomendaciones) requiere acceso rápido por user_id y orden por fecha de vista.
40. Índice en analytics.search_query_log(query_text)
Consultas populares y su frecuencia — clave para analítica de búsqueda. El índice acelera agregaciones y recuentos por texto de consulta.
Nota
Para búsquedas de texto con ILIKE se recomienda usar índices GIN con la extensión pg_trgm, que son eficientes para buscar subcadenas y búsqueda difusa. Para tablas grandes con agregación o orden por fecha, se recomienda índice DESC en la fecha — acelera la selección de los últimos registros.
Tiene sentido ajustar los índices según los planes de ejecución reales y la estadística de carga, pero los índices de arriba cubren los escenarios principales de producción de nuestro marketplace.
Añadiendo funciones
¿Aún no te has cansado? Pues vamos a escribir unas funciones más para simplificar nuestras consultas actuales y futuras. Así aceleramos la implementación de consultas clave, reducimos duplicación de código en la app y centralizamos la lógica de negocio en la base de datos.
1. Búsqueda de productos por palabra clave incluyendo tags y marcas
¿Para qué sirve?
La búsqueda normal por nombre y descripción es limitada. Muchas veces hay que buscar también por tags y marcas. Una función universal centraliza la lógica de búsqueda avanzada, reduce duplicación de código y simplifica la integración con el frontend.
2. Obtener ficha completa de producto por ID (todos los datos para la ficha)
¿Para qué sirve?
En el frontend a menudo se necesita toda la info del producto: campos principales, marca, categoría, imágenes, tags, atributos, media de puntuación y número de reseñas. La función monta la ficha completa en una sola llamada, reduciendo accesos a la BD.
3. Obtener jerarquía de categorías con anidamiento
¿Para qué sirve?
Construir el árbol (o ruta) de categorías se necesita para el escaparate, filtros y breadcrumbs. En vez de consultas recursivas en el cliente, la función devuelve toda la jerarquía de golpe.
4. Calcular precio medio y valor mínimo/máximo por categoría
¿Para qué sirve?
Para filtros en el catálogo y analítica es útil obtener estadísticas agregadas de productos en la categoría: rango de precios, media. La función evita subconsultas repetidas.
5. Comprobar y calcular automáticamente el stock de producto en todos los almacenes
¿Para qué sirve?
Permite saber al momento el stock total de un producto (y de cada variante), útil para escaparate, almacén y logística. Centraliza el cálculo y evita duplicar lógica de negocio.
6. Obtener historial de pedidos del usuario con detalles
¿Para qué sirve?
La función devuelve la lista de pedidos del usuario, incluyendo posiciones, importes, estados, permitiendo al frontend obtener el historial de una vez y montar el área personal.
7. Obtener la valoración media del usuario como vendedor/comprador
¿Para qué sirve?
Para mostrar confianza y reputación del usuario en la plataforma es importante saber su media como vendedor o comprador. La función hace el cálculo agregado.
8. Uso de código promocional por el usuario (validador con todas las condiciones)
¿Para qué sirve?
Toda la lógica de validación y uso del código promo (activo, límites, fecha, etc.) está centralizada en una función. Así se simplifica la lógica de la app y se evitan errores por duplicar condiciones.
9. Función universal de log de eventos de usuario
¿Para qué sirve?
Para analítica transversal y auditoría, el log centralizado de eventos reduce duplicación de código y el riesgo de perder datos de acciones de usuario.
10. Función para obtener saldo del monedero de bonos y suma de ingresos totales
¿Para qué sirve?
Una llamada permite obtener el saldo actual del usuario y la suma total de ingresos en el monedero. Es útil para mostrar en el dashboard y reduce el número de consultas SQL.
11. Función universal para cambiar estado de pedido con log
¿Para qué sirve?
Cambia el estado del pedido, añade registro al log de historial de estados y minimiza errores al cambiar estados en distintas partes de la app.
12. Obtener todos los mensajes del diálogo de soporte (ticket + todos los mensajes)
¿Para qué sirve?
La función devuelve toda la conversación del ticket, incluyendo detalles de la solicitud y cada mensaje. Facilita montar el historial del ticket en el frontend.
13. Comprobar existencia de usuario por email o teléfono
¿Para qué sirve?
Se usa para registro y recuperación de contraseña, evita duplicar lógica en frontend y backend.
Nota
Este set de funciones cubre los escenarios clave del negocio, mejora la gestión de datos, optimiza la lógica y acelera el desarrollo de frontend e integraciones. ¡Espero que te haya molado :)
Archivos con la solución
GO TO FULL VERSION