CODEWITHSUNDEEP
postgresql
Franz Kafka
Franz Kafka

Reputation: 844

Paginating in PostgreSQL: Knowing when to add next link

I am currently reading the guide on PostgreSQL pagination here. Now, I am using the seek method near the bottom, which looks like this:

SELECT * FROM news WHERE category_id = 1234 AND (date, id) < (prev_date, prev_id) 
ORDER BY date DESC, id DESC LIMIT 10;

After that query runs, I then need to determine if I need to add a 'next' link to the page, which I am doing by grabbing a count of all rows whose date and id are less than the date and id returned from the query. I need a next link for the infinite scroll plugin I am using. Is there a better way to determine if there are rows left after a query is done?

Upvotes: 0

Views: 83

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

One simple way to do it is to actually ask for 11 rows, even though you're showing 10. If the 11th row is present, there clearly is more data and you need the next link.

This way you don't have to ask for counts, just run one query. Then again, if the data doesn't really change, getting the count once and caching it might be more efficient. It just might be a bit error prone.

The minus side is that you will retrieve maybe 10% more data, which depending on the table structure may or may not affect performance also.

Upvotes: 1

Related Questions