Часть 5. Создание REST API: разбиение на страницы, сортировка и фильтрация вручную

В предыдущей статье вы завершили построение базовой функциональности API CRUD.

И теперь, когда на маршруте сотрудников выдается запрос HTTP GET, возвращаются все строки таблицы. Это может не иметь большого значения только с 107 строками в таблице HR.EMPLOYEES, но представьте, что произойдет, если таблица будет содержать тысячи или миллионы строк. Такие клиенты, как мобильные и веб-приложения, обычно отображают только часть строк, доступных в базе данных, а затем выбирают больше строк, когда это необходимо — возможно, когда пользователь прокручивает вниз или нажимает кнопку «Далее» на каком-либо элементе управления разбиением на страницы в пользовательском интерфейсе.

Для этого API REST должны поддерживать средства разбиения на страницы возвращаемых результатов. После того, как разбиение на страницы поддерживается, становится необходима возможности сортировки, поскольку данные обычно должны быть отсортированы до применения нумерации страниц. Кроме того, средство фильтрации данных очень важно для производительности. Зачем отправлять данные из базы данных, через промежуточный уровень и полностью на клиент, если это не нужно?

Я буду использовать параметры строки запроса URL, чтобы клиенты могли указать, как результаты должны разбиваться на страницы, сортироваться и фильтроваться. Как всегда в программировании, реализация может варьироваться в зависимости от ваших требований, целей производительности и т. д. В этом посте я расскажу вам о ручном подходе к добавлению этих функций в API.

Разбиение на страницы

Параметры строки запроса, которые я буду использовать для разбивки на страницы: skip и limit. Параметр skip будет использоваться для пропуска указанного количества строк, в то время как limit будет ограничивать количество возвращаемых строк. Я буду использовать значение по умолчанию 30 для лимита, если клиент не предоставит значение.

Начните с обновления логики контроллера, чтобы извлечь значения из строки запроса и передать их в API базы данных. Откройте файл controllers / employee.js и добавьте следующие строки кода в функцию get после строки, которая анализирует параметр req.params.id.

// *** line that parses req.params.id is here ***
    context.skip = parseInt(req.query.skip, 10);
    context.limit = parseInt(req.query.limit, 10);

Теперь необходимо обновить логику базы данных, чтобы учесть эти значения и соответствующим образом обновить запрос SQL. В SQL предложение offset используется для пропуска строк, а предложение fetch — для ограничения количества строк, возвращаемых запросом. Как обычно, значения не будут добавляться непосредственно к запросу — вместо этого они будут добавлены как переменные связывания по соображениям производительности и безопасности. Откройте db_apis / employee.js и добавьте следующий код после блока if в функции find, которая добавляет предложение where к запросу.

// *** if block that appends where clause ends here ***
 
  if (context.skip) {
    binds.row_offset = context.skip;
 
    query += '\noffset :row_offset rows';
  }
 
  const limit = (context.limit > 0) ? context.limit : 30;
 
  binds.row_limit = limit;
 
  query += '\nfetch next :row_limit rows only';

Это все, что вам нужно сделать для нумерации страниц! Запустите API, а затем выполните несколько команд URL в другом терминале, чтобы проверить его. Вот несколько примеров, которые вы можете использовать:

# use default limit (30)
curl "http://localhost:3000/api/employees"
 
# set limit to 5
curl "http://localhost:3000/api/employees?limit=5"
 
# use default limit and set skip to 5
curl "http://localhost:3000/api/employees?skip=5"
 
# set both skip and limit to 5
curl "http://localhost:3000/api/employees?skip=5&limit=5"

Сортировка

Как минимум, клиенты должны иметь возможность указать столбец для сортировки и порядок (по возрастанию или по убыванию). Самый простой способ сделать это — определить параметр запроса (я буду использовать sort), который позволяет передавать строку типа 'last_name: asc' или 'salary: desc'. Единственный способ гарантировать порядок набора результатов, возвращаемых из SQL-запроса — это включить предложение order by. По этой причине было бы неплохо иметь определение порядка по умолчанию, определяемое для обеспечения согласованности, когда клиент не указывает его.

Вернитесь в файл controllers / employee.js и добавьте следующую строку кода в функцию get после строки, которая анализирует параметр req.query.limit.

// *** line that parses req.query.limit is here ***
    context.sort = req.query.sort;

Затем откройте db_apis / employee.js и добавьте следующую строку под строками, которые объявляют и инициализируют baseQuery.

// *** lines that initalize baseQuery end here ***
 
const sortableColumns = ['id', 'last_name', 'email', 'hire_date', 'salary'];

sortableColumns — это whitelist столбцов, который клиенты смогут использовать для сортировки. Затем, внутри функции find, добавьте следующий блок if, который добавляет предложение order by. Это необходимо сделать после добавления предложения where, но до предложений offset и fetch.

// *** if block that appends where clause ends here ***
 
  if (context.sort === undefined) {
    query += '\norder by last_name asc';
  } else {
    let [column, order] = context.sort.split(':');
 
    if (!sortableColumns.includes(column)) {
      throw new Error('Invalid "sort" column');
    }
 
    if (order === undefined) {
      order = 'asc';
    }
 
    if (order !== 'asc' && order !== 'desc') {
      throw new Error('Invalid "sort" order');
    }
 
    query += `\norder by "${column}" ${order}`;
  }

Первая часть блока if проверяет, передал ли клиент значение сортировки. Если нет, то к запросу SQL добавляется предложение order by по умолчанию, которое сортирует по last_name в порядке возрастания. Если указано значение сортировки, то оно сначала разбивается на значения column и order, и каждое значение проверяется перед добавлением order by к запросу.

Теперь вы можете запустить несколько команд URL для его проверки. Вот несколько примеров, чтобы попробовать:

# use default sort (last_name asc)
curl "http://localhost:3000/api/employees"
 
# sort by id and use default direction (asc)
curl "http://localhost:3000/api/employees?sort=id"
 
# sort by hire_date desc
curl "http://localhost:3000/api/employees?sort=hire_date:desc"
 
# use sort with limit and skip together
curl "http://localhost:3000/api/employees?limit=5&skip=5&sort=salary:desc"
 
# should throw an error because first_name is not whitelisted
curl "http://localhost:3000/api/employees?sort=first_name:desc"
 
# should throw an error because 'other' is not a valid order
curl "http://localhost:3000/api/employees?sort=last_name:other"

Последние два примера должны выдавать исключения, поскольку они содержат значения, которые не были внесены в whitelist. Тут используется стандартный обработчик ошибок Express, поэтому ошибка возвращается в виде веб-страницы HTML.

Фильтрация

Возможность фильтрации данных является важной функцией, которую должны предоставлять все API REST. Как и в случае с сортировкой, реализация, может быть, простой или сложной в зависимости от того, что вы хотите поддерживать. Самый простой подход — добавить поддержку фильтров полного совпадения (например, last_name = Doe). Более сложные реализации могут добавлять поддержку базовых операторов (например, <,>, instr и т. д.) И сложных логических операторов (например, и / или), которые могут группировать несколько фильтров вместе.

В этом посте я постараюсь упростить ситуацию и добавлю поддержку фильтров только для двух столбцов: department_id и manager_id. Для каждого столбца я разрешу соответствующий параметр в строке запроса. Логика базы данных, которая добавляет предложение where, когда GET-запросы отправляются на конечную точку с одним сотрудником, необходимо обновить, чтобы учесть эти новые фильтры.

Откройте controllers / employee.js и добавьте следующие строки ниже строки, которая анализирует значение req.query.sort в функции get.

// *** line that parses req.query.sort is here ***
    context.department_id = parseInt(req.query.department_id, 10);
    context.manager_id = parseInt(req.query.manager_id, 10);

Затем отредактируйте db_apis / employee.js, добавив в базовый запрос предложение 1 = 1, как показано ниже.

const baseQuery =
 `select employee_id "id",
    first_name "first_name",
    last_name "last_name",
    email "email",
    phone_number "phone_number",
    hire_date "hire_date",
    job_id "job_id",
    salary "salary",
    commission_pct "commission_pct",
    manager_id "manager_id",
    department_id "department_id"
  from employees
  where 1 = 1`;

Конечно, 1 = 1 всегда будет принимать значение true, поэтому оптимизатор просто проигнорирует это. Однако этот метод упростит добавление дополнительных предикатов в дальнейшем.

В функции find замените блок if, который добавляет предложение where при передаче context.id, следующими строками.

// *** line that declares 'binds' is here ***
 
  if (context.id) {
    binds.employee_id = context.id;
 
    query += '\nand employee_id = :employee_id';
  }
 
  if (context.department_id) {
    binds.department_id = context.department_id;
 
    query += '\nand department_id = :department_id';
  }
 
  if (context.manager_id) {
    binds.manager_id = context.manager_id;
 
    query += '\nand manager_id = :manager_id';
  }

Как видите, каждый блок if просто добавляет значение, переданное в объект binds, а затем добавляет соответствующий предикат в предложение where. Сохраните изменения и перезапустите API. Затем используйте эти команды URL, чтобы проверить это:

# filter where department_id = 90 (returns 3 employees)
curl "http://localhost:3000/api/employees?department_id=90"
 
# filter where manager_id = 100 (returns 14 employees)
curl "http://localhost:3000/api/employees?manager_id=100"
 
# filter where department_id = 90 and manager_id = 100 (returns 2 employees)
curl "http://localhost:3000/api/employees?department_id=90&manager_id=100"

Вот и все — API теперь поддерживает разбиение на страницы, сортировку и фильтрацию! Ручной подход обеспечивает много контроля, но требует много кода. Функция поиска теперь имеет 58 строк и поддерживает только ограниченные возможности сортировки и фильтрации. Вы можете рассмотреть возможность использования модуля, такого как построитель запросов Knex.js, чтобы упростить эти операции.