The issue is that createdAt might not be unique so we could have items on a page re-displayed on the next page.
We could probably use something like a db row_index function (the index of the items in a query result) or (if not supported by the db engine) still use the id and get the X next values after the row that has that ID (probably not a simple query though).
That would complicate things lot. You can go for a timestamp value in your createdAt for example, I don't believe there will be more than one row inserted in the same microsecond, isn't it right? 🤔
Regarding timestamps, that would be a good solution indeed:
if the application does not have a lot of traffic that might introduce timestamp collisions (which is the case of my crappy apps ;))
if you already have a timestamp in your database table/collection or have the ability to add one (think about read-only legacy databases)
to avoid collisions on timestamps and be able to query next elements without duplicates, timestamps would need to be created in sequence with a random suffix based on time, not sure that this is always provided OOB by db engines.
Actually, if supported, row index is pretty straightforward, you simply add it to your select and you get an index of each row in the result, that would be the cursor value I guess.
Example here for postgresql: postgresqltutorial.com/postgresql-...
Do you see any issue on using ROW_NUMBER()?
PS: Just rambling on here, I'll probably use timestamps on my apps, as you've suggested ;)
The only issue I see with using ROW_NUMBER is that you will have to always select all results from a table, assign a row number then only pull the results you want. This is exactly how limit & offset works. So it isn't going to be of any help if your database is growing fast.
As I said if you include microseconds in your timestamp value, there mostly will be no collision.
You're right! You'd just have to have these micro-timestamps in your database and have end-users tolerant to potential (even rare) collisions otherwise I guess you'd have to deal with less performant fetching strategies.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
I see, makes sense!
The issue is that createdAt might not be unique so we could have items on a page re-displayed on the next page.
We could probably use something like a db row_index function (the index of the items in a query result) or (if not supported by the db engine) still use the id and get the X next values after the row that has that ID (probably not a simple query though).
That would complicate things lot. You can go for a timestamp value in your createdAt for example, I don't believe there will be more than one row inserted in the same microsecond, isn't it right? 🤔
Regarding timestamps, that would be a good solution indeed:
Actually, if supported, row index is pretty straightforward, you simply add it to your select and you get an index of each row in the result, that would be the cursor value I guess.
Example here for postgresql: postgresqltutorial.com/postgresql-...
Do you see any issue on using ROW_NUMBER()?
PS: Just rambling on here, I'll probably use timestamps on my apps, as you've suggested ;)
The only issue I see with using
ROW_NUMBERis that you will have to always select all results from a table, assign a row number then only pull the results you want. This is exactly howlimit&offsetworks. So it isn't going to be of any help if your database is growing fast.As I said if you include microseconds in your timestamp value, there mostly will be no collision.
You're right! You'd just have to have these micro-timestamps in your database and have end-users tolerant to potential (even rare) collisions otherwise I guess you'd have to deal with less performant fetching strategies.