如何在PostgreSQL中基于posts表主键ID实现分页？

Alright, let's work through this problem. You want to implement pagination that centers around a specific post (so when a user opens a shared link, the list starts with that post) and returns the correct page of results using postId, page, and size parameters. Here's how to make that happen:

The key here is to first find where the target post sits in your sorted list of posts, then calculate the range of records to return based on your pagination parameters. For your example, we'll assume you're sorting posts by their id in ascending order (you can swap this with the ranking field if that's your preferred sort order).

First, you need to figure out how many posts come before your target post (this gives you its starting index in the sorted list). Use this SQL query to get that count:

SELECT COUNT(*) AS posts_before
FROM posts
WHERE id < ?; -- Replace ? with the incoming postId value

In your example with postId=3, this would return 2 (since posts with ids 1 and 2 come before it). This means the target post is at index 2 (0-based) or position 3 (1-based) in the sorted list of [1,2,3,5,6,8,10,12].

Next, translate your page and size parameters into the right offset for your query. The exact calculation depends on whether you want to include the target post in your pagination results or start right after it:

Scenario 1: Include the Target Post as the First Item #

If the shared link should show the target post first, then paginate through subsequent posts:

• For page=1, you'll start at the target post and take size records
• For page=N, you'll skip (page-1)*size records starting from the target post

The offset formula here is:

offset = (page - 1) * size

And the SQL query would be:

SELECT *
FROM posts
WHERE id >= ? -- Start at or after the target post
ORDER BY id ASC
LIMIT ? OFFSET ?;

Using your example parameters (postId=3, page=1, size=3), this returns 3,5,6. For page=2, it returns 8,10,12.

Scenario 2: Start Pagination After the Target Post #

If you want to start paginating right after the target post (maybe the shared link shows the post, then the next page starts with the following content):

• The offset formula stays the same, but we adjust the WHERE clause to only include posts after the target:

SELECT *
FROM posts
WHERE id > ? -- Start after the target post
ORDER BY id ASC
LIMIT ? OFFSET ?;

For postId=3, page=1, size=3, this returns 5,6,8. For page=2, it returns 10,12.

Matching Your Example's Expected Output #

Your example expects postId=3, page=2, size=3 to return 6,8,10. This suggests a slightly adjusted logic where you're starting from the second record after the target post. To achieve this, you'd calculate the offset as:

offset = (page - 1) * size - 1

For page=2, size=3, that gives an offset of 2. The query becomes:

SELECT *
FROM posts
WHERE id > ?
ORDER BY id ASC
LIMIT ? OFFSET ?;

With parameters postId=3, size=3, offset=2, this returns 6,8,10. Just note this logic is specific to your example's expected output—you'll want to align it with how you want your pagination to behave for end users.

In your /api/posts endpoint, make sure to:

• Validate that postId exists in the posts table (return a 404 if it doesn't)
• Ensure page and size are positive integers (return a 400 error if not)
• Cap the size parameter to a reasonable maximum (like 100) to avoid performance issues with large result sets

• If the calculated offset exceeds the total number of available posts: return an empty list
• If size is 0 or a negative number: default to a reasonable value (like 10)
• If page is 0 or negative: default to page 1

内容的提问来源于stack exchange，提问作者Ahmad Alkhawaja