Laravel y PostgreSQL: técnicas avanzadas de búsqueda de texto completo, funciones de ventana y CTE en 2026

CTE recursivos para jerarquías

Supongamos que tenemos una tabla categories con el campo parent_id. Necesitamos obtener todas las subcategorías de un nodo dado del árbol:

// app/Services/CategoryService.php

public function getSubtree(int $rootId): \Illuminate\Support\Collection
{
$sql = << ' || c.name
        FROM categories c
        INNER JOIN category_tree ct ON ct.id = c.parent_id
    )
    SELECT * FROM category_tree ORDER BY depth, name
SQL;

return collect(DB::select($sql, ['root_id' => $rootId]));
}

Los CTE recursivos en PostgreSQL funcionan mediante UNION ALL: la primera parte es el caso base y la segunda es el paso recursivo que hace referencia al propio CTE. La profundidad está limitada por el parámetro max_recursion_depth (100 por defecto).

Uso del paquete staudenmeir/laravel-cte

Para integrar CTE con la sintaxis de Eloquent, instala el paquete:

composer require staudenmeir/laravel-cte

A partir de ahí, el método withExpression() estará disponible en modelos y en el Builder:

use Staudenmeir\LaravelCte\Facades\DB as CteDB;

$results = CteDB::table('orders')
->withExpression('monthly_stats', function ($query) {
    $query->from('orders')
        ->selectRaw("DATE_TRUNC('month', created_at) as month, SUM(total) as revenue")
        ->groupByRaw("DATE_TRUNC('month', created_at)");
})
->from('monthly_stats')
->orderBy('month')
->get();

Búsqueda de texto completo con PostgreSQL en Laravel

La búsqueda de texto completo integrada en PostgreSQL es órdenes de magnitud más potente que LIKE '%query%': comprende morfología, palabras vacías, ranking de relevancia y soporta operadores lógicos complejos.

Creación de un índice tsvector

Comenzamos con una migración que añade el índice para FTS:

// database/migrations/2026_01_01_000000_add_fts_to_articles.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
public function up(): void
{
    // Añadimos una columna generada para almacenar el tsvector
    DB::statement("
        ALTER TABLE articles
        ADD COLUMN search_vector tsvector
        GENERATED ALWAYS AS (
            to_tsvector('spanish', coalesce(title, '') || ' ' || coalesce(body, ''))
        ) STORED
    ");

    // Índice GIN para búsqueda rápida
    DB::statement("
        CREATE INDEX articles_search_vector_idx
        ON articles USING GIN (search_vector)
    ");
}

public function down(): void
{
    DB::statement('DROP INDEX IF EXISTS articles_search_vector_idx');
    DB::statement('ALTER TABLE articles DROP COLUMN IF EXISTS search_vector');
}
};

Búsqueda con tsquery y websearch_to_tsquery

La función websearch_to_tsquery (disponible desde PostgreSQL 11) convierte la entrada del usuario en formato de búsqueda web en un tsquery válido, protegiéndola contra inyecciones y soportando los operadores AND, OR, NOT y frases exactas entre comillas.

// app/Services/ArticleSearchService.php

use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\LengthAwarePaginator;

public function search(string $query, int $perPage = 15): LengthAwarePaginator
{
$sanitized = trim($query);

return DB::table('articles as a')
    ->select([
        'a.id',
        'a.title',
        'a.slug',
        'a.published_at',
        DB::raw("ts_rank(a.search_vector, websearch_to_tsquery('spanish', ?)) AS relevance"),
        DB::raw("ts_headline('spanish', a.body, websearch_to_tsquery('spanish', ?), 'MaxWords=50, MinWords=20, StartSel=, StopSel=') AS snippet"),
    ])
    ->join('users as u', 'u.id', '=', 'a.author_id')
    ->addSelect('u.name as author_name')
    ->whereRaw(
        "a.search_vector @@ websearch_to_tsquery('spanish', ?)",
        [$sanitized]
    )
    ->where('a.status', 'published')
    ->orderByDesc('relevance')
    ->addBinding([$sanitized, $sanitized], 'select')
    ->paginate($perPage);
}

La función ts_headline genera automáticamente un fragmento con las coincidencias resaltadas — muy útil para mostrar los resultados de búsqueda. Los parámetros StartSel y StopSel permiten definir las etiquetas HTML para el resaltado.

Búsqueda multilingüe y diccionarios personalizados

Para proyectos con varios idiomas, puedes almacenar la configuración de búsqueda en un campo separado o usar la selección dinámica de configuración:

// Búsqueda con selección dinámica de configuración de idioma

public function searchMultilang(string $query, string $lang = 'spanish'): array
{
$allowedConfigs = ['spanish', 'english', 'simple'];
$config = in_array($lang, $allowedConfigs) ? $lang : 'simple';

$sql = "SELECT id, title,
            ts_rank(
                to_tsvector(?, title || ' ' || body),
                websearch_to_tsquery(?, ?)
            ) AS rank
        FROM articles
        WHERE to_tsvector(?, title || ' ' || body) @@ websearch_to_tsquery(?, ?)
        AND status = 'published'
        ORDER BY rank DESC
        LIMIT 50";

return DB::select($sql, [$config, $config, $query, $config, $config, $query]);
}

Caché de resultados de consultas complejas con Redis

Las consultas analíticas complejas con CTE y funciones de ventana pueden tardar varios segundos. Redis es ideal para cachear esos resultados con un TTL razonable.

// app/Services/AnalyticsService.php

use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

public function getRevenueReport(string $period = '30days'): array
{
$cacheKey = "analytics:revenue:{$period}:" . now()->format('Y-m-d-H');

return Cache::store('redis')->remember($cacheKey, 3600, function () use ($period) {
    $interval = match ($period) {
        '7days' => '7 days',
        '90days' => '90 days',
        default => '30 days',
    };

    $sql = <<= NOW() - INTERVAL ':interval'
            AND status = 'completed'
            GROUP BY DATE_TRUNC('day', created_at)
        ),
        moving_avg AS (
            SELECT
                day,
                revenue,
                orders_count,
                AVG(revenue) OVER (
                    ORDER BY day
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                ) AS moving_avg_7d
            FROM daily_revenue
        )
        SELECT * FROM moving_avg ORDER BY day
    SQL;

    // Interpolación segura solo para valores de la lista blanca
    $safeSql = str_replace(':interval', $interval, $sql);

    return DB::select($safeSql);
});
}

Para invalidar la caché cuando los datos cambian, utiliza etiquetas de Redis:

// Invalidación de caché etiquetada

// Al crear un nuevo pedido:
Cache::store('redis')->tags(['analytics', 'revenue'])->flush();

// Al cachear:
return Cache::store('redis')->tags(['analytics', 'revenue'])->remember(
'revenue_report_30d',
3600,
fn() => $this->buildRevenueQuery()
);

Ten en cuenta que la caché etiquetada requiere el uso de Redis o Memcached — el driver de archivos no soporta etiquetas.

Optimización y análisis de consultas con EXPLAIN ANALYZE

Escribir una consulta es solo la mitad del trabajo. Asegurarse de que funciona de forma eficiente es la otra mitad. PostgreSQL ofrece la potente herramienta EXPLAIN ANALYZE, que muestra el plan de ejecución real de la consulta.

// app/Console/Commands/ExplainQuery.php

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ExplainQuery extends Command
{
protected $signature = 'db:explain {--format=text}';

public function handle(): void
{
    $sql = <<option('format'));
    $explain = DB::select("EXPLAIN (ANALYZE, BUFFERS, FORMAT {$format}) {$sql}");

    foreach ($explain as $row) {
        $this->line($row->{'QUERY PLAN'} ?? json_encode($row));
    }
}
}

Métricas clave a las que prestar atención en la salida de EXPLAIN ANALYZE:

• Seq Scan vs Index Scan — el escaneo secuencial en tablas grandes indica la ausencia de un índice adecuado.
• actual time — el tiempo de ejecución real de cada paso del plan.
• rows — compara la estimación del planificador con el número real de filas: una gran diferencia indica estadísticas desactualizadas.
• Buffers: shared hit/read — número de accesos a la caché de PostgreSQL y lecturas de disco.

Ejecuta ANALYZE articles; regularmente tras inserciones masivas para actualizar las estadísticas del planificador de consultas.

Caso práctico: consulta analítica con CTE + funciones de ventana + FTS

Combinemos todas las técnicas aprendidas en un ejemplo realista. La tarea: construir una página de analítica para el editor de un blog — lista de artículos con filtro FTS, ranking por relevancia, estadísticas de visualizaciones por período y comparación con el período anterior.

// app/Services/ContentAnalyticsService.php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;

public function getContentAnalytics(string $searchQuery = '', int $days = 30): array
{
$cacheKey = 'content:analytics:' . md5($searchQuery . $days) . ':' . now()->format('Y-m-d-H');

return Cache::store('redis')->remember($cacheKey, 1800, function () use ($searchQuery, $days) {

    $hasFts = !empty(trim($searchQuery));

    // Condición FTS: si la consulta está vacía, no filtramos por relevancia
    $ftsSelect = $hasFts
        ? "ts_rank(a.search_vector, websearch_to_tsquery('spanish', :query)) AS fts_rank"
        : '0 AS fts_rank';

    $ftsWhere = $hasFts
        ? "AND a.search_vector @@ websearch_to_tsquery('spanish', :query2)" : '';

    $sql = <<= NOW() - INTERVAL ':days days' THEN 1 ELSE 0 END) AS views_current,
                SUM(CASE WHEN viewed_at < NOW() - INTERVAL ':days days'
                    AND viewed_at >= NOW() - INTERVAL ':days2 days' THEN 1 ELSE 0 END) AS views_prev
            FROM page_views
            WHERE viewed_at >= NOW() - INTERVAL ':days3 days'
            GROUP BY article_id
        ),
        article_stats AS (
            SELECT
                a.id,
                a.title,
                a.slug,
                a.published_at,
                a.author_id,
                $ftsSelect,
                COALESCE(av.views_current, 0) AS views_current,
                COALESCE(av.views_prev, 0) AS views_prev,
                ROUND(
                    (COALESCE(av.views_current, 0) - COALESCE(av.views_prev, 0))::numeric
                    / NULLIF(COALESCE(av.views_prev, 0), 0) * 100, 1
                ) AS views_growth_percent
            FROM articles a
            LEFT JOIN article_views av ON av.article_id = a.id
            WHERE a.status = 'published'
            $ftsWhere
        ),
        ranked_articles AS (
            SELECT
                *,
                RANK() OVER (ORDER BY views_current DESC) AS views_rank,
                RANK() OVER (ORDER BY fts_rank DESC) AS relevance_rank,
                LAG(views_current) OVER (ORDER BY published_at) AS prev_article_views
            FROM article_stats
        )
        SELECT
            ra.*,
            u.name AS author_name,
            -- Puntuación combinada: relevancia + popularidad
            ROUND(
                (0.6 * (1.0 / NULLIF(relevance_rank, 0)) +
                 0.4 * (1.0 / NULLIF(views_rank, 0))) * 1000, 2
            ) AS combined_score
        FROM ranked_articles ra
        JOIN users u ON u.id = ra.author_id
        ORDER BY combined_score DESC
        LIMIT 50
    SQL;

    // Sustitución segura de parámetros numéricos (lista blanca)
    $daysInt = (int) $days;
    $safeSql = str_replace(
        [':days days', ':days2 days', ':days3 days'],
        ["{$daysInt} days", ($daysInt * 2) . " days", ($daysInt * 2) . " days"],
        $sql
    );

    $bindings = [];
    if ($hasFts) {
        $bindings = ['query' => $searchQuery, 'query2' => $searchQuery];
    }

    $results = $bindings
        ? DB::select($safeSql, $bindings)
        : DB::select($safeSql);

    return [
        'articles' => $results,
        'period_days' => $daysInt,
        'search_query' => $searchQuery,
        'generated_at' => now()->toIso8601String(),
    ];
});
}

Esta consulta demuestra la sinergia real de las tres técnicas:

1. CTE article_views — agrega visualizaciones de dos períodos para su posterior comparación.
2. CTE article_stats — combina los datos de los artículos con las visualizaciones y el ranking FTS.
3. CTE ranked_articles — aplica las funciones de ventana RANK y LAG al conjunto ya filtrado.
4. SELECT final — calcula la puntuación combinada y hace el join con los autores.
5. Caché con Redis — evita la ejecución repetida de la consulta pesada.

Conclusión

Eloquent y Query Builder son herramientas excelentes para el CRUD. Pero las tareas analíticas reales, las estructuras jerárquicas y la búsqueda de texto completo requieren ir más allá de sus límites. PostgreSQL ofrece un amplio arsenal: funciones de ventana para calcular rankings y dinámicas, CTE recursivos para trabajar con árboles, y FTS integrado con morfología y ranking.

En combinación con Laravel, estas capacidades son accesibles a través de DB::select(), DB::statement() y paquetes como staudenmeir/laravel-cte. Añade caché con Redis y análisis regular de planes de ejecución con EXPLAIN ANALYZE — y tu aplicación alcanzará un nuevo nivel de rendimiento y expresividad en las consultas en 2026.