Optimización de consultas complejas con CTEs (Common Table Expressions) en PHP y MySQL

Las Consultas de Tabla Comunes (CTEs), introducidas en MySQL 8.0, son una herramienta poderosa para simplificar y optimizar consultas complejas, especialmente aquellas que involucran subconsultas anidadas o lógica recursiva. En PHP, podemos aprovechar las CTEs para construir consultas más legibles y, potencialmente, más eficientes.

<?php

// Función para ejecutar consultas SQL de forma segura
function ejecutarConsulta(PDO $pdo, string $sql, array $params = []): PDOStatement
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

// Ejemplo: Calcular el promedio de ventas mensuales por categoría usando una CTE
function obtenerPromedioVentasMensuales(PDO $pdo): array
{
    $sql = "
WITH VentasMensuales AS (
    SELECT
        DATE_FORMAT(fecha_venta, '%Y-%m') AS mes,
        categoria_id,
        SUM(monto) AS total_ventas
    FROM
        ventas
    GROUP BY
        mes,
        categoria_id
)
SELECT
    vm.mes,
    c.nombre AS categoria,
    AVG(vm.total_ventas) AS promedio_ventas
FROM
    VentasMensuales vm
JOIN
    categorias c ON vm.categoria_id = c.id
GROUP BY
    vm.mes,
    c.nombre
ORDER BY
    vm.mes,
    c.nombre;
";
    $stmt = ejecutarConsulta($pdo, $sql);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Ejemplo de uso
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mi_base_de_datos", "usuario", "contraseña");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $resultados = obtenerPromedioVentasMensuales($pdo);

    foreach ($resultados as $fila) {
        echo "Mes: " . $fila['mes'] . ", Categoría: " . $fila['categoria'] . ", Promedio: " . $fila['promedio_ventas'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
>
    

En este ejemplo, la CTE "VentasMensuales" precalcula las ventas totales por mes y categoría. Esto simplifica la consulta principal, haciéndola más legible y permitiendo a MySQL optimizar la ejecución. Sin la CTE, la lógica de agregación estaría anidada dentro de la consulta principal, lo que podría resultar en una consulta más difícil de entender y potencialmente menos eficiente.

<?php
// Ejemplo de CTE recursiva (ejemplo simplificado, requiere una tabla con relaciones jerárquicas)
function obtenerDescendientes(PDO $pdo, int $id_padre): array {
    $sql = "
WITH RECURSIVE Jerarquia AS (
    SELECT id, nombre, padre_id, 1 AS nivel
    FROM elementos
    WHERE id = :id_padre

    UNION ALL

    SELECT e.id, e.nombre, e.padre_id, j.nivel + 1
    FROM elementos e
    JOIN Jerarquia j ON e.padre_id = j.id
)
SELECT id, nombre, nivel FROM Jerarquia ORDER BY nivel;
";

    $stmt = ejecutarConsulta($pdo, $sql, [':id_padre' => $id_padre]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Ejemplo de uso (asumiendo que tienes una tabla 'elementos' con campos id, nombre y padre_id)
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mi_base_de_datos", "usuario", "contraseña");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $descendientes = obtenerDescendientes($pdo, 1); // Obtener los descendientes del elemento con ID 1

    foreach ($descendientes as $fila) {
        echo "ID: " . $fila['id'] . ", Nombre: " . $fila['nombre'] . ", Nivel: " . $fila['nivel'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
>
    

Las CTEs recursivas son especialmente útiles para navegar estructuras de datos jerárquicas. El ejemplo anterior muestra cómo obtener todos los descendientes de un nodo padre en una tabla "elementos". Recuerda adaptar el nombre de la tabla y los campos a tu esquema de base de datos. La CTE recursiva se construye en dos partes: una consulta base que selecciona el nodo raíz y una consulta recursiva que se une a la CTE existente para encontrar los nodos hijos. Es crucial tener una condición de terminación implícita para evitar bucles infinitos.