0

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!

  • A MySQL-8.0 window function would be the easiest way. – danblack Apr 12 at 1:14
1

In MySQL it would be simple. Given an @id value, you can easily find the next or previous rows - and efficiently assuming there is an index on (id) and another index on (created_at):

-- find next item
select t.*
from tablename as t
     join
     ( select created_at
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
order by t.created_at asc
limit 1 ;   -- N next items

To find the previous items, you only have to change > to < and asc to desc.

If there can be rows with same created_at, then you need to modify:

-- find next item
select t.*
from tablename as t
     join
     ( select created_at, id
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
     or t.created_at = current.created_at and t.id > current.id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items

The whole current subquery is not truly needed as the values @id and @created_at will likely be available from the previous run so the query can be simplified:

-- find next item
select t.*
from tablename as t
where   t.created_at > @created_at
     or t.created_at = @created_at and t.id > @id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items
  • Thanks that look pretty good. I can see it being a problem though when two items have the same created_at. Or if sorting by another value and two or more items have the same value. I guess simply adding a 2nd sorting level (sorting by id as well) would fix that – vesperknight Apr 12 at 8:09
  • Yeah, I didn't want to complicate the answer too much but I guess that is expected (to have identical created_at values). – ypercubeᵀᴹ Apr 12 at 8:18
  • Thanks that will get me going until I'm able to upgrade to MySQL 8 (for Window methods). Now to see if I'm able to transcribe this logic into Vuex ORM query syntax so I can have a fully client-side database solution for the same problem – vesperknight Apr 12 at 8:33
  • If you want an answer for MongoDB too, either leave this open or add a new question, with the mongodb tag only. You may still get an answer here, but questions with accepted answer get less attention. – ypercubeᵀᴹ Apr 12 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.