Laravel и PostgreSQL: продвинутые техники работы с полнотекстовым поиском, оконными функциями и CTE в 2026 году

Рекурсивные CTE для иерархий

Предположим, у нас есть таблица categories с полем parent_id. Нам нужно получить все подкатегории для заданного узла дерева:

// 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]));
}

Рекурсивные CTE в PostgreSQL работают через UNION ALL: первая часть — базовый случай, вторая — рекурсивный шаг, ссылающийся на само CTE. Глубина ограничивается параметром max_recursion_depth (по умолчанию 100).

Использование пакета staudenmeir/laravel-cte

Для интеграции CTE с Eloquent-синтаксисом установите пакет:

composer require staudenmeir/laravel-cte

После этого в моделях и Builder доступен метод withExpression():

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();

Полнотекстовый поиск средствами PostgreSQL в Laravel

Встроенный полнотекстовый поиск PostgreSQL на порядок мощнее LIKE '%query%': он понимает морфологию, стоп-слова, ранжирование релевантности и поддерживает сложные логические операторы.

Создание tsvector-индекса

Начнём с миграции, добавляющей индекс для 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
 {
 // Добавляем generated-колонку для хранения tsvector
 DB::statement("
 ALTER TABLE articles
 ADD COLUMN search_vector tsvector
 GENERATED ALWAYS AS (
 to_tsvector('russian', coalesce(title, '') || ' ' || coalesce(body, ''))
 ) STORED
 ");

 // GIN-индекс для быстрого поиска
 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');
 }
};

Поиск с tsquery и websearch_to_tsquery

Функция websearch_to_tsquery (доступна с PostgreSQL 11) преобразует пользовательский ввод в формате веб-поиска в корректный tsquery, защищая от инъекций и поддерживая операторы AND, OR, NOT и точные фразы в кавычках.

// 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('russian', ?)) AS relevance"),
 DB::raw("ts_headline('russian', a.body, websearch_to_tsquery('russian', ?), '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('russian', ?)",
 [$sanitized]
 )
 ->where('a.status', 'published')
 ->orderByDesc('relevance')
 ->addBinding([$sanitized, $sanitized], 'select')
 ->paginate($perPage);
}

Функция ts_headline автоматически генерирует сниппет с выделенными совпадениями — это удобно для отображения результатов поиска. Параметры StartSel и StopSel позволяют задать HTML-теги для подсветки.

Многоязычный поиск и пользовательские словари

Для проектов с несколькими языками можно хранить конфигурацию поиска в отдельном поле или использовать динамический выбор конфигурации:

// Поиск с динамическим выбором языковой конфигурации

public function searchMultilang(string $query, string $lang = 'russian'): array
{
 $allowedConfigs = ['russian', '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]);
}

Кэширование результатов сложных запросов с Redis

Сложные аналитические запросы с CTE и оконными функциями могут выполняться секунды. Redis отлично подходит для кэширования таких результатов с разумным TTL.

// 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;

 // Безопасная интерполяция только для whitelist-значений
 $safeSql = str_replace(':interval', $interval, $sql);

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

Для инвалидации кэша при изменении данных используйте теги Redis:

// Инвалидация тегированного кэша

// При создании нового заказа:
Cache::store('redis')->tags(['analytics', 'revenue'])->flush();

// При кэшировании:
return Cache::store('redis')->tags(['analytics', 'revenue'])->remember(
 'revenue_report_30d',
 3600,
 fn() => $this->buildRevenueQuery()
);

Обратите внимание: тегированный кэш требует использования Redis или Memcached — файловый драйвер теги не поддерживает.

Оптимизация и анализ запросов с EXPLAIN ANALYZE

Написать запрос — половина дела. Убедиться, что он работает эффективно — другая половина. PostgreSQL предоставляет мощный инструмент EXPLAIN ANALYZE, который показывает реальный план выполнения запроса.

// 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));
 }
 }
}

Ключевые метрики, на которые нужно обращать внимание в выводе EXPLAIN ANALYZE:

• Seq Scan vs Index Scan — последовательное сканирование на больших таблицах сигнализирует об отсутствии подходящего индекса.
• actual time — реальное время выполнения каждого шага плана.
• rows — сравните оценку планировщика с реальным количеством строк: большое расхождение говорит об устаревшей статистике.
• Buffers: shared hit/read — количество обращений к кэшу PostgreSQL и дискового чтения.

Регулярно запускайте ANALYZE articles; после массовых вставок, чтобы обновлять статистику для планировщика запросов.

Практический кейс: аналитическая выборка с CTE + оконными функциями + FTS

Объединим все изученные техники в реалистичном примере. Задача: построить страницу аналитики для редактора блога — список статей с FTS-фильтром, ранжированием по релевантности, статистикой просмотров за период и сравнением с предыдущим периодом.

// 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));

 // FTS-условие: если запрос пустой — не фильтруем по релевантности
 $ftsSelect = $hasFts
 ? "ts_rank(a.search_vector, websearch_to_tsquery('russian', :query)) AS fts_rank"
 : '0 AS fts_rank';

 $ftsWhere = $hasFts
 ? "AND a.search_vector @@ websearch_to_tsquery('russian', :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,
 -- Комбинированный скор: релевантность + популярность
 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;

 // Безопасная подстановка числовых параметров (whitelist)
 $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(),
 ];
 });
}

Этот запрос демонстрирует реальную синергию трёх техник:

1. CTE article_views — агрегирует просмотры за два периода для последующего сравнения.
2. CTE article_stats — объединяет данные статей с просмотрами и FTS-ранжированием.
3. CTE ranked_articles — применяет оконные функции RANK и LAG к уже отфильтрованному набору.
4. Финальный SELECT — вычисляет комбинированный скор и джойнит авторов.
5. Redis-кэш — защищает от повторного выполнения тяжёлого запроса.

Заключение

Eloquent и Query Builder — отличные инструменты для CRUD. Но реальные аналитические задачи, иерархические структуры и полнотекстовый поиск требуют выйти за их рамки. PostgreSQL предоставляет богатый арсенал: оконные функции для расчёта рейтингов и динамики, рекурсивные CTE для работы с деревьями, встроенный FTS с морфологией и ранжированием.

В связке с Laravel эти возможности доступны через DB::select(), DB::statement() и пакеты вроде staudenmeir/laravel-cte. Добавьте кэширование через Redis и регулярный анализ планов через EXPLAIN ANALYZE — и ваше приложение выйдет на новый уровень производительности и выразительности запросов в 2026 году.