database - 如何从数据库获取用于Graphql中分页的光标？

Question

我在使用真正的光标来解决GraphQL中的数据库分页结果时遇到了可怕的问题。无论我使用哪种数据库(SQL(例如mysql)或NoSQL文档(例如mongodb))，都没有办法，我似乎能够获得游标或类似游标的对象。

可能我错过了一些基本概念，但是在寻找了答案之后，我开始严重怀疑官方GraphQL分页文档是否

https://graphql.org/learn/pagination/

完全基于任何真实的现场体验。

这是我的问题:如何从这样的SQL查询中获取甚至类似于游标的任何东西？

SELECT authors.id, authors.last_name, authors.created_at FROM authors
ORDER BY authors.last_name, author.created_at
LIMIT 10
OFFSET 20

我知道，不应使用基于偏移的分页，而是将基于光标的导航视为一种补救措施。而且，我绝对想从胶印病中治愈我的应用。但是为了做到这一点，我需要能够从的 检索游标。

我也明白(忘记了我读过的地方)也不应该将主键用于分页。

所以，我被困在这里。

Answer 1

我认为您因提出一个好的问题而被否决。 first / last / before / after概念很难在SQL中实现。
我一直在为同样的问题而烦恼。分页文档未介绍应用自定义ORDER语句时如何定义游标。
而且我也没有真正在网上找到全面的解决方案。我找到了一些人们正在解决该问题的帖子，但是答案仅部分正确或部分完整(只是base64编码ID字段以使游标看起来像是首选答案，但这几乎没有说明查询的内容)必须计算游标)。同样，任何涉及row_number的解决方案都很难看，并且不适用于不同的SQL方言。因此，让我们尝试不同的方法。
快速免责声明，这将是一篇相当全面的文章，但是如果您的后端使用了不错的查询构建器，则可以从技术上编程一种方法，该方法可用于将Relay GraphQL所需的第一个/最后/之前/之后/之后的分页实现到上任何预先存在的查询。唯一的要求是，所有要排序的表都具有一栏，该栏唯一地代表记录的默认顺序(通常，如果主键是整数并且使用自动生成的ID，即使从技术上讲，您也可以使用该列)通过其主键对表进行排序并不总是会获得与无序返回表相同的结果)
暂时不要使用base64，只需假设ID是代表表默认顺序的有效游标字段即可。
您在网上找到的使用光标的答案通常是这样。

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

好吧，这很有效，因为它不将任何其他种类应用于查询，所以可以将所有条目作为游标之后的字符串，例如LONG。一旦您在示例中使用了自定义排序，此建议就会失效。
但是，可以将其中的核心逻辑重新用于各种查询，但是需要扩展解决方案。让我们尝试提出完整的算法。

c 之后 的前n个算法(光标之后的前n个节点)
节点或边与SQL术语中的行相同。 (如果1行代表一个实体，例如1位作者)
当游标是该行之后的行时，我们将开始返回同级行，无论它是向前还是向后。
给定 C 是光标
是与 C 比较的任何其他行。
T 是 A 和 C 均为行的表。
并且 v w x y z 是表 T 上的5列，很自然， A 和 C 都有这些列。
该算法必须基于给定n的游标对象以及这5列提供的顺序来决定是否将A从返回查询中包括或排除。
让我们从一个订单开始。
给定1个顺序(v):(如果我们假设默认情况下我们的表按其主键排序，则该顺序至少应始终存在)
为了显示的前n个记录，我们将需要应用限制n n ，这是微不足道的。困难的部分是c 之后的 。
对于仅由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

在那里，我们找到了最简单的解决方案，为“未排序”表提供光标。这与处理游标的普遍接受的答案是相同的解决方案，但是不完整。
现在，让我们看一下按两列排序的查询( v 和 w ):

 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.v)的C值的任何行。但是，如果第一阶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种排序的逻辑相同，但要多做一些。如果第一列相同，我们需要查看第二列以了解谁是最大的列。如果第二列也相同，则需要查看第三列。重要的是要认识到，主键始终是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

这是一个令人恐惧的比较。对于添加的每个订单，计算c 之后第n个 所需的比较次数将增加，每一行执行Triangular Number。幸运的是，我们可以应用一些 bool(boolean) 代数来压缩和优化此查询。

 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

即使将其压缩，图案也仍然很清晰。每条条件线在OR与AND之间改变，每条条件线在>和=之间改变，最后每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;

该查询将根据查询的类型正确返回ID为15的作者之后的前20个作者。
如果您不喜欢使用变量或辅助查询，则也可以使用子查询:

  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时，该查询也需要在子查询中应用。
您不需要在a.id上显式调用ORDER，但是我这样做是为了与算法保持一致。如果您使用DESC而不是ASC，这确实变得非常重要。
那么，如果使用DESC列而不是ASC会发生什么呢？算法会中断吗？好吧，如果您套用一些额外的小规则，那就不行了。对于使用DESC而不是ASC的任何列，都可以将'>'符号替换为'<'，并且该算法现在可以在两个方向上进行排序。
联接对这个算法没有影响(感谢上帝)，除了联接表中的20行不一定代表20个实体(在这种情况下为20个作者)这一事实之外，但这是一个与整个前/后无关的问题。问题，您还将使用OFFSET。
处理已经具有预先存在的WHERE条件的查询也不是特别困难。您只需获取所有预先存在的条件，将它们包装在方括号中，然后将它们与AND语句结合起来即可使用算法生成的条件。
在那里，我们实现了一种算法，该算法可以处理任何输入查询并使用first / after对其进行正确分页。 (如果我错过了一些极端情况，请告诉我)
而且你可以停在那里，但是...不幸的是
您仍然需要处理前n个，后n个，c 之前的 ，，c 之后的，在c 之前的最后n个，最后一个在c 和和ojit之前的最后n个符合GraphQL Relay规范，完全摆脱了偏移:)。
您可以使用我刚刚提供的给定AFTER算法获得一半。但是对于另一半，您需要在 -algorithm之前使用 。它与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确定操作员必须指向哪个方向。)
对于“前n个”，除了对查询应用“LIMIT n”外，您无需执行任何操作。
对于“最后一个n”，您需要应用“LIMIT n”并反转所有给定的ORDERS，将ASC与DESC切换，将DESC与ASC切换。 'last n'有一个警告，尽管它将正确地返回最后n条记录，但它会以相反的顺序进行，因此您需要再次手动反向返回的集，无论是在数据库中还是在代码内部。
有了这些规则，您就可以使用唯一的可排序列(通常是主键)作为游标，将来自Relay GraphQL规范的任何分页请求成功集成到任何SQL查询中，该游标代表表示表默认排序的事实来源。
这相当艰巨，但我设法使用这些算法为Doctrine DQL构建器编写了一个插件，以使用MySQL数据库实现分页方法中的first / last / before / after方法。因此，这绝对是可行的。