Запросы к каталогу

В предыдущем уроке каталог получил операции создания, замены и удаления. Однако список по-прежнему читает все строки из PostgreSQL, а затем отбирает подходящие объекты в памяти приложения. При четырёх курсах это почти незаметно. Для большой библиотеки такой подход заставляет передавать ненужные строки и мешает ограничивать размер ответа. Перенесём отбор и страницу в запрос базы, сохранив адрес /api/courses.

Полный проект находится в lesson-17/after архива продолжения. Его start точно продолжает after урока 16: отдельная учебная база, четыре курса, 60 уроков. Версии остаются прежними: .NET 10 и Npgsql 10.0.0. Команды, SQL и учебный сервер при подготовке не выполнялись; далее описаны ожидаемые ответы. Другие ветки архива выбираются отдельно, их схемы нельзя автоматически смешивать.

Страница имеет определённый порядок

Страница — ограниченная часть упорядоченного результата. Если записать только LIMIT, база вправе вернуть разные строки при другом плане выполнения. Нам нужен устойчивый порядок, включая случай одинакового названия. Для учебного списка выбираем ORDER BY id COLLATE "C": ID уникален, а явная сортировка делает порядок наших латинских идентификаторов воспроизводимым независимо от локали базы.

Сохраним minLessons и q, добавим page и pageSize. Нумерация начинается с единицы, размер по умолчанию равен двум, максимальный размер — пятидесяти. Номер ограничим десятью тысячами, чтобы не принимать бессмысленно большие смещения и не переполнять вычисление. Отсутствующее значение получает умолчание, некорректное число даёт 400 при binding, число вне диапазона — 400 с предметной ошибкой.

Смещение вычисляется после проверки:

var offset = (page - 1) * pageSize;

Здесь первая страница пропускает ноль строк, вторая при размере два — две строки. Ответ сохраняет прежний JSON-массив курсов: переход к пагинации не добавляет самовольно обёртку с total. Это всё же изменение поведения: клиент, ожидавший весь каталог, должен явно переходить по страницам. Совместимость адреса не означает неизменность объёма выдачи.

Для пустого фильтра и первой страницы ожидаются HTML/CSS и JavaScript. Для второй — Markdown и производительность. Третья страница содержит пустой массив и статус 200. Отсутствие строки в списке отличается от обращения к отсутствующему /api/courses/{id}, где остаётся 404.

Значения передаются параметрами

Новый метод repository получает уже проверенные значения. Полный запрос использует позиционные параметры Npgsql:

SELECT id, title, topic, lessons, internal_notes
FROM courses
WHERE lessons >= $1
  AND ($2 = '' OR strpos(lower(title), lower($2)) > 0)
ORDER BY id COLLATE "C"
LIMIT $3 OFFSET $4;

Первый параметр задаёт минимум, второй — очищенную поисковую строку, третий — размер, четвёртый — смещение. Имена столбцов и направление сортировки остаются частью фиксированного SQL. Нельзя сделать произвольный пользовательский sort безопасным, просто передав его вместо имени столбца: параметр обозначает значение, а не синтаксическую конструкцию. Для нескольких порядков понадобилась бы явная таблица разрешённых вариантов.

Поиск реализован через strpos, поэтому процент и подчёркивание являются обычными символами. При использовании ILIKE они означали бы шаблон, если отдельно не согласовать экранирование. Это небольшая, но важная часть контракта: запрос пользователя должен иметь предсказуемый смысл. Начальные и конечные пробелы убираются, пустая строка не ограничивает набор.

Сравнение регистра теперь выполняет PostgreSQL через lower, а не .NET через OrdinalIgnoreCase. Для наших названий и запроса javascript ожидается JavaScript, но полную языковую эквивалентность двух реализаций мы не обещаем. Если продукт требует одинакового поведения для особых Unicode-случаев, правило нормализации нужно выбрать отдельно и проверить на реальном корпусе.

Название поиска ограничено ста двадцатью Unicode-скалярами и не принимает нулевой символ. Это уменьшает случайные чрезмерные запросы и согласуется с ограничением названия курса. Ограничение длины не заменяет параметризацию SQL: даже короткую строку нельзя склеивать с текстом запроса.

Фильтр применяется раньше страницы

Рассмотрим обращение:

GET /api/courses?minLessons=16&page=1&pageSize=2
Accept: application/json

Сначала условие выбирает HTML/CSS с шестнадцатью уроками и JavaScript с двадцатью. Затем порядок располагает их по ID, после чего берётся страница. Ожидается два объекта с суммой 36. Вторая страница того же фильтра пуста. Нельзя сначала взять первую пару исходных строк, а потом фильтровать её: это дало бы короткие страницы и могло бы скрыть подходящий курс за пределами выбранного окна.

Если добавить q=javascript, остаётся один объект с двадцатью уроками. При слове, которого нет в названиях, возвращается []. Число minLessons=501, нулевая страница и размер 51 отклоняются до обращения к repository. Пустой результат не должен подменять ошибку неверных параметров: иначе клиент не сможет отличить отсутствие материалов от ошибки собственной формы.

Repository материализует только выбранные строки. Reader и command закрываются внутри метода через await using; токен отмены передаётся в выполнение и чтение. Наружу не уходит живой курсор, который удерживал бы соединение после завершения обработчика. Поля внутренней заметки остаются в сущности и исчезают при знакомом преобразовании в публичный DTO.

Где заканчивается простая пагинация

OFFSET удобен для небольшого учебного каталога, но большой номер страницы обычно требует пройти пропускаемую часть результата. Маленький размер ответа не доказывает маленький объём работы базы. Мы не измеряли план и не объявляем этот запрос оптимальным для миллионов строк. Выбор индекса и поиск по подстроке относятся к отдельному исследованию нагрузки.

Кроме того, два последовательных HTTP-запроса не разделяют один снимок базы. Между ними другой редактор может добавить или удалить курс. Тогда часть элементов сдвинется между страницами. Уникальный порядок убирает неопределённость сортировки, но не замораживает набор. Для ленты с частыми изменениями полезна пагинация по ключу: клиент передаёт последний виденный ID, а сервер выбирает следующие. Это другой публичный контракт, его нельзя незаметно включить вместо номера страницы.

Упражнение для будущего самостоятельного выполнения: прочитайте две страницы пустого фильтра, затем повторите их с минимумом шестнадцать. Сопоставьте не только количество, но и конкретные ID. После изменения параметров всегда начинайте с первой страницы. Здесь мы получили ограниченную, упорядоченную SQL-выдачу; следующий урок рассмотрит уже не чтение, а согласованную запись курса и его аудита.

Правила порядка и страницы описаны в документации PostgreSQL 17 о LIMIT/OFFSET, работа параметров и reader — в руководстве Npgsql.