SQL 对数据进行分页，其中分页从给定的主键开始

Question

编辑: 我使用的原始示例有一个 int 作为主键，而实际上我的主键是一个包含 UUID 作为字符串的 var char。我已经更新了下面的问题以反射(reflect)这一点。

警告:解决方案必须适用于 postgres。

问题:当从已知页码或索引开始到要分页的结果列表时，我可以轻松地对数据进行分页，但是如果我所知道的只是行的主键，那么如何做到这一点开始于。例如说我的表有这个数据

TABLE: article
======================================
id           categories    content
--------------------------------------
B7F79F47     local         a
6cb80450     local         b
563313df     local         c
9205AE5A     local         d
E88F7520     national      e
5ab669a5     local         f
fb047cf6     local         g
591c6b50     national      h
======================================

给定一篇文章主键 '9205AE5A' (article.id == '9205AE5A') 并且类别列必须包含 'local' 如果它是分页的，即返回的结果应包含 3 个项目(上一篇、当前、下一篇)

('563313df','local','c'),('9205AE5A','local','d'),('5ab669a5','local','f')

这是我的示例设置:

-- setup test table and some dummy data
create table article (
  id varchar(36),
  categories varchar(256),
  content varchar(256) 
)
insert into article values
('B7F79F47', 'local', 'a'),
('6cb80450', 'local', 'b'),
('563313df', 'local', 'c'),
('9205AE5A', 'local', 'd'),
('E88F7520', 'national', 'e'),
('5ab669a5', 'local', 'f'),
('fb047cf6', 'local', 'g'),
('591c6b50', 'national', 'h');

我想对文章表中的行进行分页，但我的起点是文章的“id”。为了在呈现的页面上提供“上一篇文章”和“下一篇文章”链接，我还需要这篇文章两边的文章我知道

在服务器端，我可以运行我的分页 sql 并遍历每个结果集以找到给定项目的索引。请参阅以下低效的伪代码/sql 来执行此操作:

page = 0;
resultsPerPage = 10;
articleIndex = 0;
do {
    resultSet = select * from article where categories like '%local%' limit resultsPerPage offset (page * resultsPerPage) order by content;
    for (result in resultSet) {
        if (result.id == '9205AE5A') {
           // we have found the articles index ('articleIndex') in the paginated list. 
           // Now we can do a normal pagination to return the list of 3 items starting at the article prior to the one found
           return select * from article where categories like '%local%' limit 3 offset (articleIndex - 1);
        }
        articleIndex++;
    }
    page++;
} while (resultSet.length > 0);

如果给定的文章在分页列表的下方，这会非常慢。如果没有丑陋的 while+for 循环，如何做到这一点？

编辑 2:我可以使用两个 sql 调用得到结果

SELECT 'CurrentArticle' AS type,* FROM
  (
   SELECT (ROW_NUMBER() OVER (ORDER BY content ASC)) AS RowNum,* 
   FROM article 
   WHERE categories LIKE '%local%' 
   ORDER BY content ASC
  ) AS tagCloudArticles
WHERE id='9205AE5A' 
ORDER BY content ASC 
LIMIT 1 OFFSET 0

从该结果返回例如

('CurrentArticle', 4, '9205AE5A', 'local', 'd')

我可以得到 RowNum 值(4)，然后再次运行 sql 得到 RowNum+1(5)和 RowNum-1(3)

SELECT 'PrevNextArticle' AS type,* FROM
  (
   SELECT (ROW_NUMBER() OVER (ORDER BY content ASC)) AS RowNum,* 
   FROM article 
   WHERE categories LIKE '%local%' 
   ORDER BY content ASC
  ) AS tagCloudArticles
WHERE RowNum in (3, 5)
ORDER BY content ASC 
LIMIT 2 OFFSET 0

结果

   ('PrevNextArticle', 3, '563313df', 'local', 'c'),
   ('PrevNextArticle', 5, '5ab669a5', 'local', 'f')

不过，如果能在一个高效的 sql 调用中执行此操作会很好。

Answer 1

如果页面显示的周边文章信息只有“Next”和“Previous”，则无需提前获取它们的行数。当用户选择“上一个”或“下一个”时，使用这些查询 SQL Fiddle

-- Previous
select *
from article
where categories = 'local' and id < 3
order by id desc
limit 1
;

-- Next
select *
from article
where categories = 'local' and id > 3
order by id
limit 1
;

---

如果需要获取上一篇文章和下一篇文章的信息:SQL Fiddle

with ordered as (
    select
        id, content,
        row_number() over(order by content) as rn
    from article
    where categories = 'local'
), rn as (
    select rn
    from ordered
    where id = '9205AE5A'
)
select
    o.id,
    o.content,
    o.rn - rn.rn as rn
from ordered o cross join rn
where o.rn between rn.rn -1 and rn.rn + 1
order by o.rn

文章将有 rn -1, 0, 1，如果存在的话。