So I have this problem in many of my apps and I don't even know how to describe it correctly.
I'm using MongoDB (3.6) so my IDs are hashes.
I have a table/grid view, which displays items mapped to database records, then when someone clicks on one you get the single-item view page. But then from there, I want to allow next and previous "pagination".
I quote "pagination" because the URL never contains a page number, and can't. The url only contains a direct link to an item.
For example: mydomain.com/posts/59118d075e04da6104421bb4
Let's say to keep it simple, items have a created_at timestamp and the ordering is always by created_at ascending.
You have 100 items, you land on item 54 out of 100. But you don't know that because the id is a hash. Maybe it was 54th in the ordering when you bookmarked it and now since more items have been added it's now 58th out of 104.
The API returns the data for that item, but then what happens when the user wants to navigate to the previous or next item. What's the most efficient way to handle that?
A very slow way would be to query ALL items ordered by created_at, then loop through the list until you find the one that matches the current ID. And then -1 +1 that would be your previous/next item.
And every time a user clicks the prev/next arrow you repeat this slow process.
I have the same problem in MySQL (5.7). I don't know how to say "given this ID, what is the prev/next records based on this ordering"
Another way I thought of doing this is storing a persistent array of all the IDs, and then getting the position for the item that matches a given ID, and then you could more easily get the IDs of items +1 -1 that position. That wouldn't work too well if you had a huge number of records though.
Is there a more efficient way? Hope someone can enlighten me.. thank you!