CODEWITHSUNDEEP
sqlpostgresqlpagination
Shane Rowatt
Shane Rowatt

Reputation: 2105

SQL to Paginate Data Where Pagination Starts at a Given Primary Key

Edit: The original example I used had an int for the primary key when in fact my primary key is a var char containing a UUID as a string. I've updated the question below to reflect this.

Caveat: Solution must work on postgres.

Issue: I can easily paginate data when starting from a known page number or index into the list of results to paginate but how can this be done if all I know is the primary key of the row to start from. Example say my table has this data

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
======================================

Given an article primary key of '9205AE5A' (article.id == '9205AE5A') and categories column must contain 'local' what sql can I use to return a result set that includes the articles either side of this one if it was paginated i.e. the returned result should contain 3 items (previous, current, next articles)

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

Here is my example setup:

-- 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');

I want to paginate the rows in the article table but the starting point I have is the 'id' of an article. In order to provide a "Previous Article" and "Next Article" links on the rendered page I also need the articles that would come either side of this article I know the id of

On the server side I could run my pagination sql and iterate through each result set to find the index of the given item. See the following inefficient pseudo code / sql to do this: 

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);

This is horrendously slow if the given article is way down the paginated list. How can this be done without the ugly while+for loops?

Edit 2: I can get the result using two sql calls 

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

From that result returned e.g.

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

I can get the RowNum value (4) and then run the sql again to get RowNum+1 (5) and 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

with result

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

It would be nice to do this in one efficient sql call though.

Upvotes: 3

Views: 2212

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125574

If the only information about the surrounding articles shown in the page is "Next" and "Previous" there is no need to get their rows in advance. When the user chooses "Previous" or "Next" use these queries 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
;

If it is necessary to get information about the previous and next articles: 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

The articles will have rn -1, 0, 1, if existent.

Upvotes: 3

Nandakumar V
Nandakumar V

Reputation: 4635

I think this query will yield you the result

(SELECT *, 2 AS ordering from article where categories like '%local%' AND id = 3 LIMIT 1)
UNION
(SELECT *, 1 AS ordering from article where categories like '%local%' AND id < 3 ORDER BY id DESC LIMIT 1 )
UNION
(SELECT *, 3 AS ordering from article where categories like '%local%' AND id > 3 ORDER BY id ASC LIMIT 1 )

Upvotes: 0

Sandeep
Sandeep

Reputation: 1210

Check whether following query solve your issue. passed id as well in filter with category:

SELECT * FROM
(
select (1 + row_number() OVER(Order BY id ASC)) AS RowNo,* from article where categories like '%local%' and id>=3
UNION
(SELECT 1,* FROM article where categories like '%local%' and id<3 ORDER BY id DESC LIMIT 1)
) AS TEMP
WHERE
  RowNo between 1 and (1+10-1)
ORDER BY
  RowNo

Upvotes: 0

Related Questions