Как получить курсор для разбиения на страницы в Graphql из базы данных?

Я думаю, что вас заминусовали за то, что вы задали хороший вопрос. Концепцию «первый/последний/до/после» сложно реализовать в SQL.

Я ломал голову над той же проблемой. В документации по разбивке на страницы не рассматривается, как определять курсоры при применении пользовательских операторов ORDER.

И я действительно не нашел комплексного решения в Интернете. Я нашел несколько сообщений, в которых люди обращаются к этой проблеме, но ответы являются лишь частично правильными или частично полными (просто base64 кодирует поле идентификатора, чтобы курсор казался подходящим ответом, но это мало говорит о том, что на самом деле является запросом) должен сделать, чтобы вычислить курсор). Кроме того, любые решения, связанные с row_number, довольно уродливы и неприменимы для разных диалектов SQL. Итак, давайте попробуем по-другому.

Быстрый отказ от ответственности, это будет довольно всеобъемлющий пост, но если ваш сервер использует достойный построитель запросов, вы можете технически запрограммировать метод, который работает для реализации первого/последнего/до/после разбиения на страницы, требуемого Relay GraphQL, на ЛЮБЫЕ. ранее существовавший запрос. Единственное требование состоит в том, чтобы во всех таблицах, которые вы сортируете, был столбец, который однозначно представляет порядок записей по умолчанию (обычно, если ваш первичный ключ является целым числом и использует автоматически сгенерированные идентификаторы, вы можете использовать его, даже если технически упорядочивание таблицы по ее первичному ключу не всегда дает тот же результат, что и возврат таблицы в неупорядоченном виде)

Забудьте на мгновение о base64 и просто предположите, что идентификатор является допустимым полем курсора, представляющим порядок таблицы по умолчанию.

Ответ, который вы найдете в Интернете для использования курсора, обычно таков.

SELECT * FROM TABLE T
WHERE T.id > $cursorId;

Ну, это прекрасно работает, чтобы получить все записи после курсора, ПОКА вы не применяете какие-либо другие виды к запросу. Как только вы используете пользовательскую сортировку, как в вашем примере, это предложение не работает.

Однако основная логика может быть повторно применена для запросов с сортировкой, но решение необходимо расширить. Попробуем составить полный алгоритм.

Алгоритм для первая н после с(первые n узлов после курсора)

Узел или ребро — это то же самое, что и строка в терминологии SQL. (если 1 строка представляет один объект, например 1 автора)

Пока курсор — это строка, после которой мы начнем возвращать родственные строки, будь то вперед или назад.

Данный С является курсором

А — любая другая строка, сравниваемая с С.

Т — это таблица, в которой А и С являются строками.

А v w x y z — это 5 столбцов в таблице Т, естественно, эти столбцы есть и в А, и в С.

Алгоритм должен решить, включен ли A или исключен из возвращаемого запроса, на основе объекта курсора, заданного n и предоставленных порядков этих 5 столбцов.

Начнем с одного заказа.

Учитывая, что существует 1 порядок (в): (который всегда должен быть, по крайней мере, если мы предполагаем, что наша таблица упорядочена по ее первичному ключу по умолчанию) Чтобы показать первые n записей, нам нужно применить предел п, что тривиально. Сложная часть — после с.

Для таблицы, которая упорядочивается только по 1 полю, это будет сводиться к:

 SELECT A FROM T
 WHERE A.v > C.v
 ORDER BY T.v ASC
 LIMIT n

Это должно показать все строки, у которых v больше, чем C, и удалить все строки, у которых v меньше, чем у C, что означает, что перед C не останется ни одной строки. Если мы предположим, что первичный ключ правильно представляет естественный порядок, мы может отбросить оператор ORDER BY. Тогда немного более удобочитаемая версия этого запроса станет:

 SELECT A FROM T
 WHERE A.id > $cursorIdGivenByClient
 LIMIT n

И вот мы пришли к простейшему решению для предоставления курсора в «несортированную» таблицу. Это то же решение, что и общепринятый ответ для работы с курсорами, но, увы, неполный.

Теперь рассмотрим запрос, отсортированный по двум столбцам (в и ж):

 SELECT A FROM T
 WHERE A.v > C.v
 OR (A.v = C.v AND A.w > C.w)
 ORDER BY T.v ASC, T.w ASC
 LIMIT n

Мы начинаем с того же WHERE A.v > C.v, любая строка, для которой значение v (A.v) меньше, чем значение C для первой сортировки (C.v), удаляется из выходного результата. Однако, если столбцы для первого порядка v имеют одинаковое значение как для A, так и для C, A.v = C.v нам нужно посмотреть на столбец второго порядка, чтобы увидеть, разрешено ли отображение A в результатах запроса. Что будет, если A.w > C.w

Перейдем к запросу с 3 сортировками:

 SELECT A FROM T
 WHERE A.v > C.v
 OR (A.v = C.v AND A.w > C.w)
 OR (A.v = C.v AND A.w = C.w AND A.x > C.x)
 ORDER BY T.v ASC, T.w ASC, T.x ASC
 LIMIT n

Это та же логика, что и для 2 сортов, но немного более проработанная. Если первый столбец такой же, нам нужно посмотреть на второй столбец, чтобы увидеть, кто самый большой. Если второй столбец ТАКЖЕ такой же, нам нужно посмотреть на 3-й столбец. Важно понимать, что первичный ключ всегда является последним столбцом сортировки в операторе ORDER BY и последним условием для сравнения. В этом случае A.x > C.x (или A.id > $cursorId)

В любом случае должна начать возникать закономерность. Для сортировки по 4 столбцам запрос будет таким:

 SELECT A FROM T
 WHERE A.v > C.v
 OR (A.v = C.v AND A.w > C.w)
 OR (A.v = C.v AND A.w = C.w AND A.x > C.x)
 OR (A.v = C.v AND A.w = C.w AND A.x = C.x AND A.y > C.y)
 ORDER BY T.v ASC, T.w ASC, T.x ASC, T.y ASC
 LIMIT n

И, наконец, для сортировки по 5 столбцам.

 SELECT A FROM T
 WHERE A.v > C.v
 OR (A.v = C.v AND A.w > C.w)
 OR (A.v = C.v AND A.w = C.w AND A.x > C.x)
 OR (A.v = C.v AND A.w = C.w AND A.x = C.x AND A.y > C.y)
 OR (A.v = C.v AND A.w = C.w AND A.x = C.x AND A.y = C.y AND A.z > C.z)
 ORDER BY T.v ASC, T.w ASC, T.x ASC, T.y ASC, T.z ASC
 LIMIT n

Это страшное количество сравнений. Для каждого добавленного заказа количество сравнений, необходимых для расчета первая н после с, увеличивается на Треугольный номер, выполняемое для каждой строки. К счастью, мы можем применить некоторую логическую алгебру, чтобы сжать и оптимизировать этот запрос.

 SELECT A FROM T
 WHERE (A.v > C.v OR
           (A.v = C.v AND 
              (A.w > C.w OR
                   (A.w = C.w AND
                       (A.x > C.x OR
                           (A.x = C.x AND
                               (A.y > C.y OR
                                    (A.y = C.y AND
                                        (A.z > C.z)))))))))
 ORDER BY T.v ASC, T.w ASC, T.x ASC, T.y ASC, T.z ASC
 LIMIT n

Даже после его сгущения картина вполне четкая. Каждая строка условия изменяется между ИЛИ и И, и каждая строка условия изменяется между > и = , наконец, каждые 2 строки условия мы сравниваем столбец следующего порядка.

И это сравнение также удивительно эффективно. В среднем половина всех строк будет квалифицирована после первой проверки A.v > C.v и на этом остановится. А из другой половины, которая все-таки пройдет, большинство потерпит неудачу при второй проверке A.v = C.v и остановится на этом. Поэтому, хотя это может генерировать большие запросы, я бы не слишком беспокоился о производительности.

Но давайте конкретизируем и используем это, чтобы дать вам ответ о том, как использовать курсор для рассматриваемого примера:

 SELECT authors.id, authors.last_name, authors.created_at FROM authors
 ORDER BY authors.last_name, author.created_at

Является ли ваш базовый запрос отсортированным, но еще не разбитым на страницы.

Ваш сервер получает запрос на отображение "первых 20 авторов после автора с курсором" После расшифровки курсора выясняем, что он представляет автора с id 15.

Сначала мы можем запустить небольшой предварительный запрос, чтобы получить необходимую информацию:

 $authorLastName, $authorCreatedAt =
      SELECT authors.last_name, authors.created_at from author where id = 15;

Затем применяем алгоритм и подставляем поля:

  SELECT a.id, a.last_name, a.created_at FROM authors a
  WHERE (a.last_name > $authorLastName OR
            (a.last_name = $authorLastName AND 
               (a.created_at > $authorCreatedAt OR
                    (a.created_at = $authorCreatedAt AND
                        (a.id > 15)))))
 ORDER BY a.last_name, a.created_at, a.id
 LIMIT 20;

Там этот запрос корректно вернет первых 20 авторов после автора с ID 15 в соответствии с типами запроса.

Если вам не нравится использовать переменные или вторичные запросы, вы также можете использовать подзапросы:

  SELECT a.id, a.last_name, a.created_at FROM authors a
  WHERE (a.last_name > (select last_name from authors where id 15) OR
            (a.last_name = (select last_name from authors where id 15) AND 
               (a.created_at > (select created_at from authors where id 15)  OR
                    (a.created_at = (select created_at from authors where id 15) AND
                        (a.id > 15)))))
 ORDER BY a.last_name, a.created_at, a.id
 LIMIT 20;

Опять же, это не так плохо, как кажется, подзапросы не коррелированы, а результаты будут кэшироваться по циклам строк, поэтому это не будет особенно плохо для производительности. Но запрос становится беспорядочным, особенно когда вы начинаете использовать JOINS, которые также необходимо применять в подзапросах.

Вам не нужно явно вызывать ORDER для a.id, но я делаю это, чтобы соответствовать алгоритму. Это становится очень важным, если вы используете DESC вместо ASC.

Так что же произойдет, если вы используете столбцы DESC вместо ASC? Алгоритм ломается? Ну нет, если вы примените небольшое дополнительное правило. Для любого столбца, использующего DESC вместо ASC, вы замените знак «>» на «<», и теперь алгоритм будет работать для сортировки в обоих направлениях.

СОЕДИНЕНИЯ не влияют на этот алгоритм (слава богу), за исключением того факта, что 20 строк из объединенных таблиц не обязательно будут представлять 20 объектов (в данном случае 20 авторов), но это проблема, которая не зависит от всего сначала/после вопрос, который вы также получите, используя OFFSET.

Также несложно обрабатывать запросы, которые уже имеют ранее существовавшие условия WHERE. Вы просто берете все ранее существовавшие условия, заключаете их в скобки и объединяете оператором AND с условиями, сгенерированными алгоритмом.

Там мы реализовали алгоритм, который может обрабатывать любой входной запрос и правильно разбивать его на страницы, используя сначала/после. (Если есть крайние случаи, которые я пропустил, дайте мне знать)

И на этом можно было бы остановиться, но... к сожалению

Вам по-прежнему нужно обрабатывать первый н, последний н, до с, после с, последняя н перед с, последняя n после c и первая н перед с, если вы хотите соответствовать спецификациям GraphQL Relay и полностью избавиться от смещения :).

Вы можете пройти половину пути, используя данный AFTER-алгоритм, который я только что предоставил. Но для другой половины вам нужно будет использовать алгоритм ДО. Это очень похоже на алгоритм ПОСЛЕ:

 SELECT A FROM T
 WHERE (A.v < C.v OR
           (A.v = C.v AND 
              (A.w < C.w OR
                   (A.w = C.w AND
                       (A.x < C.x OR
                           (A.x = C.x AND
                               (A.y < C.y OR
                                    (A.y = C.y AND
                                        (A.z < C.z)))))))))
 ORDER BY T.v ASC, T.w ASC, T.x ASC, T.y ASC, T.z ASC
 LIMIT n

Чтобы получить алгоритм BEFORE, вы берете алгоритм AFTER и просто переключаете все операторы '<' на операторы '>' и наоборот. (По сути, до и после — это один и тот же алгоритм, где ДО/ПОСЛЕ + ASC/DESC решает, в каком направлении должен указывать оператор.)

Для «first n» вам не нужно ничего делать, кроме применения «LIMIT n» к запросу.

Для «последнего n» вам нужно применить «LIMIT n» и отменить все заданные ORDERS , переключая ASC на DESC и DESC на ASC. Есть одно предостережение с 'last n' , хотя он будет правильно возвращать последние n записей, он будет делать это в обратном порядке, поэтому вам нужно снова вручную отменить возвращенный набор, будь то в вашей базе данных или внутри вашего кода.

Там с этими правилами вы можете успешно интегрировать любые запросы на разбивку на страницы из спецификации Relay GraphQL в любой SQL-запрос, используя уникальный сортируемый столбец, часто первичный ключ, в качестве курсора, который представляет источник истины для сортировки таблицы по умолчанию.

Это довольно сложно, но мне удалось написать плагин для построителя Doctrine DQL, используя эти алгоритмы для реализации методов разбивки на страницы первым/последним/до/после с использованием базы данных MySQL. Так что это определенно выполнимо.